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

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'

CodePudding user response：

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

CodePudding user response：

Your where condition is filtering the records and making only those records to return which have data in uc hence no benefit of left join. You can do (uc.deleted=0 or uc.deleted is null) or Move the condition uc.deleted with on condition with join

CodePudding user response：

check if uc.deleted=0 and remove the where condition. the and handles the non null condition of the left join

SELECT sg.date, sg.groupName, sg.highlights, 
        user.display_name as displayName,
        COUNT(uc.groupName) as cmtcnt
FROM dbo.saved_groups as sg
    JOIN dbo.user on user.email = sg.userName
    LEFT JOIN dbo.user_comments as uc on sg.groupName = uc.groupName
    AND uc.deleted = 0
GROUP BY sg.groupName