MySQL JOINED table query should return zero but instead does not return those rows

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'

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