Multiple Join on same table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Join on same table

Author

Topic

Fratton
Starting Member

5 Posts

Posted - 2014-01-01 : 18:47:28

Dear Collective

I am struggling with joining on the same table and cannot seem to get my head around the solution.

I have three tables as follows:

Patients Samples Results
UniqueID UniqueID
Surname LabNumber LabNumber
Forename (etc.) Sampled Code
Result

The code is the name of the test and the result is just that. Not all patients will have the same set of results. What I'd now like to do is pull out a CSV file of all results from 2013 including where the result is null. The format I am looking for is along the following lines:

Sex, Age, AKIN2, AKIN7, RIFLE,

The AKIN2, AKIN7 and RIFLE are the codes in the result table. I have tried OUTER JOINS but it seems to only pull out those records that exist (example below). I need to specify the result codes I am interested in otherwise the output could be enormous but this then does not pull out null values (a NULL value is an important as an actual value).

I'm not an SQL programmer but this is part of a PhD on acute kidney injury so please treat me gently.

Thanks

Graham

SELECT
Samples.Patient_ID AS ID,
Samples.LabNo As LabNo,
Patients.Sex As Sex,
DATEDIFF(year, Patients.DoB, Samples.Sampled) AS Age,
Samples.Source as Source,
Creat.Result As Creat,
eGFR.Result as eGFR,
AKIN2.Result AS AKIN2,
AKIN7.Result as AKIN7,
AKIN30.Result as AKIN30,
AKIN90.Result as AKIN90,
RIFle.Result AS RIFLE,
DIFF.Result As Diff,
AKI.Result AS ACB,
Delta.Result As Delta
FROM Samples
FULL OUTER JOIN Results as Creat ON Samples.LabNo = Creat.LabNo
Full OUTER JOIN Results as eGFR on Samples.LabNo = eGFR.LabNo
FULL OUTER JOIN Results as AKIN2 on Samples.LabNo = AKIN2.LabNo
FULL OUTER JOIN Results as AKIN7 ON Samples.LabNo = AKIN7.LabNo
FULL OUTER JOIN Results as AKIN30 ON Samples.LabNo = AKIN30.LabNo
FULL OUTER JOIN Results as AKIN90 ON Samples.Labno = AKIN90.Labno
FULL OUTER JOIN Results as RIFLE ON Samples.LabNo = RIFLE.LabNo
FULL OUTER JOIN Results as Diff On Samples.LabNo= Diff.LabNo
FULL OUTER JOIN Results as AKI ON Samples.LabNo = AKI.LabNo
FULL OUTER JOIN Results as Delta ON Samples.LabNo = Delta.LabNo
INNER JOIN Patients ON Samples.Patient_ID = Patients.Unique_ID
WHERE year(Samples.sAMPLED) = '2013'
AND Creat.Code = 'CRE2'
AND AKIN2.Code = 'AKIN2'
AND AKIN7.Code = 'AKIN7'
AND eGFR.Code = 'eGFR'
AND AKIN30.Code = 'AKIN30'
AND AKIN90.Code = 'AKIN90'
AND RIFLE.Code = 'RIFLE'
AND Diff.Code = 'DIFF_CRE2'
AND AKI.Code = 'AKI'
AND Delta.Code = 'DELTA_CRE2'

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-01 : 19:33:50

Move the lines from your where section, to the join section - and you probably could do with left oyter join instead of full outer join.

select Samples.Patient_ID as ID
      ,Samples.LabNo as LabNo
      ,Patients.Sex as Sex
      ,datediff(year,Patients.DoB,Samples.Sampled) as Age
      ,Samples.Source as Source
      ,Creat.Result as Creat
      ,eGFR.Result as eGFR
      ,AKIN2.Result as AKIN2
      ,AKIN7.Result as AKIN7
      ,AKIN30.Result as AKIN30
      ,AKIN90.Result as AKIN90
      ,RIFle.Result as RIFLE
      ,DIFF.Result as Diff
      ,AKI.Result as ACB
      ,Delta.Result as Delta 
  from Samples 
       left outer join Results as Creat
                    on Samples.LabNo=Creat.LabNo
                   and Creat.Code='CRE2'
       left outer join Results as eGFR
                    on Samples.LabNo=eGFR.LabNo 
                   and eGFR.Code='eGFR'
       left outer join Results as AKIN2
                    on Samples.LabNo=AKIN2.LabNo
                   and AKIN2.Code='AKIN2'
       left outer join Results as AKIN7
                    on Samples.LabNo=AKIN7.LabNo 
                   and AKIN7.Code='AKIN7'
       left outer join Results as AKIN30
                    on Samples.LabNo=AKIN30.LabNo 
                   and AKIN30.Code='AKIN30'
       left outer join Results as AKIN90
                    on Samples.Labno=AKIN90.Labno
                   and AKIN90.Code='AKIN90'
       left outer join Results as RIFLE
                    on Samples.LabNo=RIFLE.LabNo 
                   and RIFLE.Code='RIFLE'
       left outer join Results as Diff
                    on Samples.LabNo=Diff.LabNo
                   and Diff.Code='DIFF_CRE2'
       left outer join Results as AKI
                    on Samples.LabNo=AKI.LabNo
                   and AKI.Code='AKI'
       left outer join Results as Delta
                    on Samples.LabNo=Delta.LabNo
                   and Delta.Code='DELTA_CRE2'
       inner join Patients
               on Patients.Unique_ID=Samples.Patient_ID
 where year(Samples.sAMPLED)='2013'

Fratton
Starting Member

5 Posts

Posted - 2014-01-01 : 19:48:48

You are an absolute star - worked perfectly from the pasted code.

Many thanks and Happy New Year

Graham

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources