CODEWITHSUNDEEP
sqlmysqljoin
Dustin Sun
Dustin Sun

Reputation: 5532

Mysql SQL join 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?

Upvotes: 0

Views: 95

Answers (3)

ashurexm
ashurexm

Reputation: 6335

Try changing left join to right outer join

Upvotes: 0

araqnid
araqnid

Reputation: 133422

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.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

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

Upvotes: 2

Related Questions