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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.