Filter Left Join

Question

How to properly filter data in left join?

My task is to create a view of the current load of support units by calculating the number of requests assigned to each one of the support units:

There are two tables in my MS SQL database :

• Contact, which contains Support Units
• Case, which contains requests

If I do a simple left join, the output data is OK:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact 
left join [Case] on Contact.Id = [Case].OwnerId
Group By Contact.Id; 

Output:

Contact	Count
id1	0
Id2	2
id3	0
Id4	1

The problem here is that I only need cases that have a certain status:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact 
left join [Case] on Contact.Id = [Case].OwnerId
Where [Case].StatusId = 'some id here'

I receive this:

Contact	Count
Id2	2
Id4	1

The records that have 0 in column Count disappear from the result.

How do I overcome this?

Answer 1

You need to add it to the join statement:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact 
left join [Case] on Contact.Id = [Case].OwnerId 
and [Case].StatusId = 'some id here'
Group By Contact.Id;

Answer 2

If you want the output both (with count >0 and count==0). You can add or condition like:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact
 left join [Case] on Contact.Id = [Case].OwnerId
 Where ([Case].StatusId = 'some id here' or [Case].id is null);