Outer joins and summing values


I have the following tables and show the relevant columns
RefCaseId (PK)

OrganId (PK)
TransplantCount (integer value... generally 1 or 2)

RefCaseId (FK)
OrganId (FK)

what I want is a sum of all the TransplantCount's for every referralcase where the organ outcome code is 'TRAN' (transplanted).

Resultset like this, for example
RefCaseId Transplants
1001              5
1002              1
1003              4

My problem occurs (I think) when a ReferralCase has no entry in the ReferralCaseOrgan table AND/OR when there IS a record but the outcome is not 'TRAN'
1004              0

I thought I could use left joins but no go....
,  sum(O.TransplantCount) [Transplants]
from ReferralCase RC
left join ReferralCaseOrgan RCO on RC.RefCaseId = RCO.RefCaseId
left join Organ O on RCO.OrganId = O.OrganId

where RCO.OrganOutcomeCd = 'TRAN'

group by RC.RefCaseId

I get 31 records back.  I should get 36 records, of which 5 will have [Transplants] = 0 because either (a)  there was no record in the ReferralCaseOrgan table or more likely (b) none of the records for that refcaseId have an outcome of 'TRAN'.
LVL 10
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SELECT rfc.RefCaseId, SUM(org.TransplantCount) AS [Transplants]
FROM referralCase rfc
LEFT OUTER JOIN referralCaseOrgan rfco ON rfco.RefCaseId = rfc.RefCaseId AND
    rfco.OrganOutcomeCd = 'TRAN'
LEFT OUTER JOIN organ org ON org.OrganId = rfco.OrganId
ibostAuthor Commented:
OMG I ask this question then I delete because I thought I had found the answer.... Then I have re-ask because I didn't... Now I figure it out.

I'll still give points if anyone will explain why this makes a difference:

instead of "where RCO.OrganOutcomeCd = 'TRAN'" in the where clause, if I move it to the join itself everything works:

left join ReferralCaseOrgan RCO on RC.RefCaseId = RCO.RefCaseId AND RCO.ORGANOUTCOMECD = 'TRAN'

This gives me Transplants of NULL which is easy to fix in the select clause:
isnull(sum(O.TransplantCount), 0) [Transplants]
ibostAuthor Commented:
hehe Hi Scott

We crisscrossed posts :)

Can you tell me why it has to be in the join?  I think I know the answer but would rather just hear it from a pro.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Change the where to  Coalesce(RCO.ORGANOUTCOMECD,'TRAN') = 'TRAN'

will produce the same results as putting the criteria into the LEFT JOIN.  It will allow rows that don't match to be selected, the way you proposed the where is the same as an INNER JOIN.  

By the way, since my post doesn't address the original question, no points to me.
Scott PletcherSenior DBACommented:
As we know, the LEFT JOIN will insure that the RC table is kept, even if RCO and O do not match.  If SQL can't find a match for them, it automatically sets all column values from that table(s) to NULL.  So, when SQL gets to the WHERE:

where RCO.OrganOutcomeCd = 'TRAN'

OrganOutcomeCd is NULL, and NULL is never equal to anything, so the test fails and SQL doesn't include the row.

WHERE (RCO.OrganOutcomeCd IS NULL OR RCO.OrganOutcomeCd = 'TRAN')

is, to me, the clearest way of testing for that.  But putting it in the WHERE may help SQL include fewer rows from the join in the initial result, so I've started checking it there :-) .

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ibostAuthor Commented:
Ok that is sort of what I thought...  the WHERE is evaluated after.  

For some reason I initially was thinking the WHERE was evaluated first, so all the base tables were filtered down and THEN joined together (filling in NULLs where appropriate).  Instead it goes the other way around.

Folderol - actually I tried your way and it does fix the original issue.  It's probably not how I'll implement but it's a neat trick I never thought of.  I like learning tricks :)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.