user1693763 user1693763 - 1 year ago 130
MySQL Question

Getting movie lists and rating from two tables MySQL LEFT JOIN

I have a table 'movies' with fields movie_id,movie_name,language,cast and another table 'ratings' with fields id,movie_id,rating and comment.

I need to fetch all movies details with average and total count of ratings.

If there is no rating for any movie, average rating and total ratings count must be zero.

for this i tried this MySQL query:

SELECT m.movie_id,movie_name,cast,language,AVG(rating) as average_rating,COUNT(rating) as total_rating FROM movies m LEFT JOIN ratings r ON m.movie_id=r.movie_id;

But this query not giving all movies lists from 'movies' table. Please help. Thank you.

Answer Source

Use group by with movie_id so it will differentiate your result with unique movie_id.

SELECT m.movie_id,m.movie_name,m.cast,m.language,AVG(r.rating) as average_rating,COUNT(r.rating) as total_rating FROM movies m 
LEFT JOIN ratings r ON m.movie_id=r.movie_id
GROUP BY m.movie_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download