Amy Neville Amy Neville - 5 months ago 12
SQL Question

LEFT JOIN return all rows despite GROUP BY

I'm trying to get the LEFT JOIN query to include all rows from the reviews table:

SELECT r.review_id, SUM(v.vote_good), SUM(v.vote_bad)
FROM reviews AS r
LEFT JOIN reviews_votes_overall AS v
ON r.review_id = v.vote_review_id
GROUP BY v.vote_review_id
ORDER BY SUM(v.vote_good)


Unfortunately because some reviews don't have corresponding votes, the GROUP BY is causing rows from the reviews table not to be returned.

How do I return all rows from the reviews table in this query?

Answer

You should use GROUP BY r.review_id. When a review doesn't have any votes, v.vote_review_id is NULL. When you group by v.vote_review_id, all records with zero votes will be grouped into a single row in the resultset, with review_id as NULL.

SELECT r.review_id, SUM(v.vote_good), SUM(v.vote_bad)
FROM reviews AS r
LEFT JOIN reviews_votes_overall AS v
ON r.review_id = v.vote_review_id
GROUP BY r.review_id
ORDER BY SUM(v.vote_good)