mysql newbie - joining

Question

I have a table of movies and a table of votes. Users vote for movies they like. I need to display the list of movies descending on the total votes for the movie. What I have now sort of works. The only problem is it doesn't show movies with 0 votes.

SELECT m.name, m.imdb_url, m.comment, COUNT(v.movie_id) AS votes
FROM movies m, votes v
WHERE v.movie_id=m.movie_id
GROUP BY v.movie_id
ORDER BY votes DESC

Answer 1

You need a to do an outer join; what you have coded is an implicit inner join.

A LEFT OUTER JOIN will grab all rows from the "left" table, and any matching records from the right table. Any left table records missing matching records in the right table will have null for the right table values. You can turn those nulls into 0 in a number of ways.

Answer 2

Does your table have value of 0 for movies without a vote, or does it use NULL/EMPTY?

If it records the zero, then make your SQL something like this:

"SELECT movies WHERE votes >= 0"

Answer 3

You probably need to do a left join, which includes records in the left table (movies) even if there are no matches in the right table (votes).

Answer 4

As per the advice from peacedog:

SELECT m.name, m.imdb_url, m.comment, COUNT(v.movie_id) AS votes

FROM movies m
left outer join votes v
on v.movie_id=m.movie_id

GROUP BY v.movie_id
ORDER BY COUNT(v.movie_id) DESC

I'm not sure about mysql, but not all databases allow you to use the alias in the ORDER BY bit, so I've replaced votes with COUNT(v.movie_id).

Answer 5

What I believe you're looking for is a left join. Your sql would look like:

SELECT m.name, m.imdb_url, m.comment, COUNT(v.movie_id) AS votes
FROM movies AS m 
LEFT JOIN votes AS v ON m.movie_id = v.movie_id
GROUP BY v.movie_id
ORDER BY votes DESC