I have a query like this
SELECT m.title FROM Movie AS M WHERE (SELECT MAX(r.stars) FROM rating AS r WHERE r.mID = M.mID);
I want to show the MAX(r.stars) in my result set, what am I doing wrong?
I don't think you need a sub query here. Does this give you what you need? If so it should be more efficient.
SELECT m.title, MAX(r.stars) FROM Movie AS m LEFT OUTER JOIN rating AS r ON r.mID = m.mID; GROUP BY m.title
Only way it could break that I can see is if you have two movies with the same title and different mID's.
WHERE with a
LEFT OUTER JOIN, or
INNER JOIN if you only want movies with ratings.
SELECT m.title, r.MaxRating FROM Movie AS M LEFT OUTER JOIN ( SELECT mID, MAX(r.stars) AS MaxRating FROM rating GROUP BY mID )AS r ON r.mID = M.mID;