I want to display result set, Counts for all Level whether it is Zero "0" or Not in SQL Se-CodePudding

I am trying to run this query:

SELECT 
    M.Desc AS LevelDescription, 
    COUNT(M.Id) AS Counts
FROM 
    Master_Levels M 
LEFT JOIN 
    Counsent C ON (M.Id = C.LevelId)
GROUP BY 
    M.Desc, M.Id, C.LevelId, C.ProcedureId, C.IsAccepts
HAVING 
    (C.IsAccepts = 'Yes') 
    AND (C.ProcedureId = 3) 
    AND (M.Id IN (1, 2, 3, 4))

It shows only non-zero counts, I want result like this:

result

I hope somebody can help me.

CodePudding user response：

I think the restrictions in the HAVING clause belong in the respective ON clause of the joins:

SELECT M.Desc AS LevelDescription, COUNT(C.LevelId) AS Counts
FROM Master_Levels M
LEFT JOIN Counsent C
    ON M.Id = C.LevelId AND C.ProcedureId = 3 AND C.IsAccepts = 'Yes'
WHERE M.Id IN (1, 2, 3, 4)
GROUP BY M.Desc;