CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10581

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?

Upvotes: 0

Views: 143

Answers (1)

Joyce Babu
Joyce Babu

Reputation: 20654

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)

Upvotes: 2

Related Questions