Returning data when no corresponding rows in second table
-
I want to perform a left join between two tables, summing the values, and have it return a value for each row in the left table, even if there is not a corresponding row in the right table. How do I do that?
For example, here is the SQL Select I've got so far:
Select p.DisplayName, count(r.ClientNumber) as 'Count'
from Providers p
left join Referral r
on p.ProviderNumber = r.ProviderNumber and p.Facility = r.Facility
where r.DateReferred between '2005-11-1' and '2005-11-30'
and p.Condition1 = 1
group by p.DisplayName
As you can see, the two tables share a common column value. However, I know that the Referral table may not have records for the month of November 2005, however I would still like to get a result of that for each provider and have the count be equal to 0.
How do I do that?
Doctor Who -
move you where clause for the Referal Table into the join:
Select p.DisplayName, count(r.ClientNumber) as 'Count'
from Providers p
left join Referral r
on p.ProviderNumber = r.ProviderNumber and
p.Facility = r.Facility and
r.DateReferred between '2005-11-1' and '2005-11-30'
where
p.Condition1 = 1
group by p.DisplayName
* Noel -
noeld, I tried doing what I think you suggested, and got the following:
Select p.DisplayName, count(r.ClientNumber) as 'Count'
from Providers p
left join Referral r
on p.ProviderNumber = r.ProviderNumber and p.Facility = r.Facility and p.Condition1 = 1
where r.DateReferred between '2005-11-1' and '2005-11-30'
group by p.DisplayName
But it still didn't give me what I wanted. If I perform a simple select against the Provders table with the condition that Condition1 = 1, I will get 7 rows returned. However, both the select that I originally against the two tables give and the modification that you suggested each gave a resultset of 6 rows.
Have I misunderstood your suggestion?
Doctor Who -
Yep, you misunderstood.
If you left-join a table, but then proceed to reference that table in the WHERE, SQL Server converts it to an Inner join
>>where r.DateReferred between '2005-11-1' and '2005-11-30'
You're still referencing "r", the left-joined table, in your Where therefore it gets converted to an Inner Join. Move the condition into the join as Noel correctly suggested.
-
OK, PW, I now see what you are saying. I've changed the SELECT to look like this:
Select p.DisplayName,
count(r.ClientNumber) as 'Count'
from Providers p
left join Referral r
on p.ProviderNumber = r.ProviderNumber and p.Facility = r.Facility
and r.DateReferred between '2005-11-1' and '2005-11-30'
where p.Condition1 = 1
group by p.DisplayName
Now I am getting 7 rows, which is what I wanted to get.
However, now I'm also getting an error message from SQL Server which says:
Warning: Null value is eliminated by an aggregate or other SET operation.
What does that mean, and what do I do about it?
Doctor Who -
Also, please note that if your DateReferred contains date and time values you will need to change that statement to:
And r.DateReferred >= '2005-11-1' And r.DateReferred < '2005-12-01'
Otherwise you will miss records on 11/30/2005 with a time value greater than 00:00:00.000
-ron
-
However, now I'm also getting an error message from SQL Server which says:
Warning: Null value is eliminated by an aggregate or other SET operation.
What does that mean, and what do I do about it?
it is just a warning NOT an error. It means the Count() function did not include any of the null values (missing column values from the left join).
-
It's not an error. It says it's warning.
It means some of values in r.ClientNumber are NULL (they must be because of left join) and COUNT eliminates them when counting. It counts only NOT NULL values.
Same rule about SUM, AVG, etc.
_____________
Code for TallyGenerator -
If you want to avoid the warning, you can set ansi_warnings off
/Kenneth
-
Thank you, Neal, PW, Ron, Sergiy and Kenneth for your help.
Doctor Who -
You are welcome
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply