Mysql SQL join question

Question

I am trying to find all deals information along with how many comments they have received. My query

select deals.*,
       count(comments.comments_id) as counts
from deals 
left join comments on comments.deal_id=deals.deal_id 
    where cancelled='N'

But now it only shows the deals that have at least one comment. What is the problem?

Answer 1

Try changing left join to right outer join

Answer 2

You missed GROUP BY

select deals.*, count(comments.comments_id) as counts 
from deals 
left join comments on comments.deal_id=deals.deal_id 
where cancelled='N'
GROUP BY deals.deal_id

Answer 3

Is 'cancelled' a column of comments? If so, you need to move "cancelled='N'" from the WHERE clause to the ON clause (from deals left join comments on comments.deal_id = deals.deal_id AND comments.cancelled='N') or do the filtering in an inline view (from deals left join (select * from comments where cancelled='N') comments on comments.deal_id = deals.deal_id) since filtering it in the where clause will drop all rows where cancelled is null.