[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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'.
  • 3
  • 2
  • 2
2 Solutions
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.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 :-) .
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 :)


Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now