SQL Server 2008 Join type to use for 4 tables

Question

I have a patient lab database created by a colleague in MS Access that has 4 tables.

Table 1 contains Patient Demographics (Patient ID, Gender, DOB, etc.)

Table 2 contains Type1 (Blood Pressures, Heart rate)

Table 3 contains Type2 (Chemistry panel, hemoglobin, etc.)

Table 4 contains Type3 (Microbiology culture results)

Each table has an internal patient number in common. Each measure can be taken independently of the other, and may or may not have results. What I would like is a simple table that pulls an external patient ID, gender and DOB only found in Table 1, combined with the DATE of results from each of the other tables. I tried using RIGHT joins and expected there to be 'null' values when there was no match on dates in subsequent tables. Instead I have a result that looks like a cross-tab query and each row has a value for each. Obviously I am not understanding the use of JOINS correctly. This is what I tried:

SET DATEFORMAT dmy
DECLARE @time time(0)

Select DISTINCT
P.PATIENT_ID as 'Patient ID',
P.SEX as 'Sex',
CONVERT(VARCHAR(11),P.DOB) as 'DOB',
CONVERT(VARCHAR,TYPE1.DateTime,101)as 'BP Measure DATES',
CONVERT(VARCHAR,TYPE2.DateTime,101) as 'CHEM Measure DATES',
CONVERT(VARCHAR,TYPE3.DateTime,101) as 'MICRO Measure DATES'
From dbo.PATIENT as P LEFT JOIN dbo.BRACHIALBPS as TYPE1 on P.PATIENT_NO=TYPE1.PATIENT_NO
Right Join dbo.Chemistries as TYPE2 on P.PATIENT_NO=TYPE2.PATIENT_NO
Right Join dbo.Microbiologies as TYPE3 on P.PATIENT_NO=TYPE3.PATIENT_NO
ORDER BY P.PATIENT_ID

I am using SQL Server 2008 to import the data and execute the query.

As I mentioned I was hoping to have one row per patient/per result date. But I am getting multiple rows for EVERY date combination.

Thanks in advance for any guidance!

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Please try the following version of the query:

SET DATEFORMAT dmy
DECLARE @time time(0)

Select DISTINCT
    P.PATIENT_ID as 'Patient ID',
    P.SEX as 'Sex',
    CONVERT(VARCHAR(11),P.DOB) as 'DOB',
    CONVERT(VARCHAR,TYPE1.DateTime,101)as 'BP Measure DATES',
    CONVERT(VARCHAR,TYPE2.DateTime,101) as 'CHEM Measure DATES',
    CONVERT(VARCHAR,TYPE3.DateTime,101) as 'MICRO Measure DATES'
From 
    dbo.PATIENT as P 
    LEFT JOIN dbo.BRACHIALBPS as TYPE1 
        on P.PATIENT_NO=TYPE1.PATIENT_NO
    LEFT Join dbo.Chemistries as TYPE2 
        on P.PATIENT_NO=TYPE2.PATIENT_NO
    LEFT Join dbo.Microbiologies as TYPE3 
        on P.PATIENT_NO=TYPE3.PATIENT_NO
ORDER BY 
    P.PATIENT_ID

RIGHT joins were replaced by LEFT joins.

When there was RIGHT JOIN dbo.BRACHIALBPS as TYPE1 on P.PATIENT_NO=TYPE1.PATIENT_NO in the query it "told" the databse engine to take ALL the applicable (not fiiltered out) rows from dbo.BRACHIALBPS as TYPE1 table and try to find matching row in table dbo.PATIENT as P - if the match wasn't found, fill the "missing fields" of table P for a particular result-record with NULL's.

Using LEFT join would prioritize dbo.PATIENT as P so the rows in join process are first taken from this table and then match is tried to be found in left-joined table dbo.BRACHIALBPS as TYPE1. Again if there was no match - "missing fields" from TYPE1 in query result would be filled with NULL's.

There is an interesting thread on SO, where JOINS were explained very detailed and from quite many aspects - please look at: Difference between INNER and OUTER joins.

I hope I understood the problem correctly and it helps at least slightly.

Thank you T_G, I would give you an up vote, but as a peon I can't yet! This helped me immensely. It has worked better than my original, but I still have to tweak it a bit more. When I run each query separately, I only have about 35 dates from each set (Type 1, Type 2, Type 3). But when I run the query as you suggested I have 174 results. It is still matching up every possible variation of matching dates. I think I either need to run the query based on dates or join the sub-query. But, you have definitely given me a direction to move! — Rolo Tomasi, Jan 29 '15 at 11:15
I'm glad could help a little then. If I came with something potentially useful I'll write. Regards. — , Jan 29 '15 at 11:37

score 0 · Answer 2 · answered Jan 30 '15 at 15:35

Working on this a bit more, I used temp tables and joined them to obtain the result I needed. Not sure if there is a better or more efficient way, but it worked. If there are any comments or concerns with what I did, would love to hear the input.

SET DATEFORMAT dmy
DECLARE @time time(0)

Select DISTINCT 
PATIENT.PATIENT_ID as 'PatientID',
PATIENT.SEX as 'Sex',
CONVERT(VARCHAR(11),PATIENT.DOB) as 'DOB'
INTO #PAT
From PATIENT

SELECT DISTINCT
P.PATIENT_ID as 'PatientID',
CONVERT(VARCHAR,TYPE1.DateTime,101)as 'BP Measure DATES'
INTO #BP
FROM dbo.PATIENT as P Inner JOIN dbo.BRACHIALBPS as TYPE1 on P.PATIENT_NO=TYPE1.PATIENT_NO

SELECT DISTINCT
P.PATIENT_ID as 'PatientID',
CONVERT(VARCHAR,TYPE2.DateTime,101) as 'CHEM Measure DATES'
INTO #CHEM
FROM dbo.PATIENT as P Inner JOIN dbo.Chemistries as TYPE2 on P.PATIENT_NO=TYPE2.PATIENT_NO

SELECT DISTINCT
P.PATIENT_ID as 'PatientID', 
CONVERT(VARCHAR,TYPE3.DateTime,101) as 'MICRO Measure DATES'
INTO #MIC
From dbo.PATIENT as P Inner JOIN dbo.Microbiologies as TYPE3 on P.PATIENT_NO=TYPE3.PATIENT_NO

SELECT DISTINCT
#PAT.PatientID,
#PAT.DOB,
#PAT.Sex,
#BP.[BP Measure DATES],
#CHEM.[CHEM Measure DATES],
#MIC.[MICRO Measure DATES]
FROM #PAT
Left JOIN #BP on #PAT.PATIENTID=#BP.PATIENTID
Left Join #CHEM on #PAT.PatientID=#CHEM.PatientID and #BP.[BP Measure DATES]=#CHEM.[CHEM Measure DATES]
Left Join #MIC on #PAT.PatientID=#MIC.PatientID and #BP.[BP Measure DATES]=#MIC.[MICRO Measure DATES]

SQL Server 2008 Join type to use for 4 tables

2 Answers2