SELECT sg.date, sg.groupName, sg.highlights, user.display_name as displayName, COUNT(uc.groupName) as cmtcnt FROM `saved_groups` as sg LEFT JOIN `user` on user.email = sg.userName LEFT JOIN `user_comments` as uc on sg.groupName = uc.groupName WHERE uc.deleted=0 GROUP BY sg.groupName
I have two tables, saved_groups
and user_comments
and the second line of the query should (does, it works) return an extra column with the number of comments associated with each group.
However, when a group has zero comments associated with it, that group is simply not returned. The only rows returned are those that have > 0
comments. Desired behavior is for all groupNames to be returned, and specify 0 for those rows that have zero associated comments in the uc table.
How can I fix this query?
I tried: IF(uc.deleted=1, 0, COUNT(uc.groupName)) as cmtcnt
— but that makes no difference, the same results are returned.
At this point, I’m unsure what next to try.
Suggestions?
Update:
Tried this:
SELECT sg.date, sg.groupName, sg.highlights, user.display_name as displayName, COUNT(uc.groupName) as cmtcnt FROM geom.saved_groups as sg JOIN geom.user on user.email = sg.userName JOIN geom.user_comments as uc on sg.groupName = uc.groupName WHERE isnull(uc.deleted,0) in (0,1) GROUP BY sg.groupName
Got:
#1582 - Incorrect parameter count in the call to native function 'isnull'
Advertisement
Answer
You want all records? Then remove the WHERE
clause. You want 0
for the records with no count? Use COALESCE
. Like this:
SELECT sg.date , sg.groupName , sg.highlights , user.display_name as displayName , COALESCE(COUNT(uc.groupName), 0) as cmtcnt FROM `saved_groups` as sg LEFT JOIN `user` on user.email = sg.userName LEFT JOIN `user_comments` as uc on sg.groupName = uc.groupName AND uc.deleted = 0 -- to get only comments that have not been deleted, and not the deleted ones GROUP BY sg.groupName