SQL Count across 3 tables

Question

I have a query that works fine when there is data but not when I have nothing in the charities table. Any help would be hugely appreciated.

SELECT  C.CategoryId
    , C.CategoryName
    , ISNULL(COUNT(CC.CharityId), 0) as CharityCount
    , C.IsDeleted
FROM    Charity.Categories C
LEFT JOIN Charity.CharityCategories CC on C.CategoryId = CC.CategoryId
LEFT JOIN Charity.Charities CH ON CC.CharityId = CH.CharityId
WHERE CH.IsApproved = 1 and CH.IsDeleted = 0
GROUP BY C.CategoryId, C.CategoryName , C.IsDeleted
Order By C.CategoryName

I am basically trying to retrieve all of the available Charity.Categories with the counts of the approved and not deleted charities.

Answer 1

Try changing it thus:

SELECT  C.CategoryId 
    , C.CategoryName 
    , ISNULL(COUNT(CC.CharityId), 0) as CharityCount 
    , C.IsDeleted 
FROM    Charity.Categories C 
LEFT JOIN Charity.CharityCategories CC on C.CategoryId = CC.CategoryId 
LEFT JOIN Charity.Charities CH ON CC.CharityId = CH.CharityId 
    AND CH.IsApproved = 1 and CH.IsDeleted = 0 
GROUP BY C.CategoryId, C.CategoryName , C.IsDeleted 
Order By C.CategoryName 

By referencing CH (Charities) in the WHERE clause you set it such that when those values were NULL (i.e no record in charities matches) then the data from the other tables is also excluded.

I generally try to include all constraints/filters in the join clause wherever possible for just this reason.