CODEWITHSUNDEEP
t-sqlsql-server-2008count
Simon Hazelton
Simon Hazelton

Reputation: 1255

SQL Count across 3 tables

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.

Upvotes: 0

Views: 175

Answers (1)

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

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.

Upvotes: 4

Related Questions