I have the following tables and show the relevant columns
TransplantCount (integer value... generally 1 or 2)
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
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'
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'.