query to return null values when none exist in one table

Question

I need to list out 6 medical offices in one column, and the patient's full name in the other. If the patient has never visited that office before, then a NULL should appear.

The table structures are simple with MedicalOffice having an ID, Name, and a few miscellaneous columns.
MedicalProcedure has the officeID, patientID, patient's full name, and some miscellaneous columns.

There is only one ID per office in the MedicalOffice table, but MedicalProcedure can have many officeIDs per patient and even the same officeID for the same patient more than once.

Following some answers I found here on SO, I tried a left outer join:

select  m.Name, p.FullName
from MedicalOffice m
left outer join MedicalProcedure p ON  m.ID = p.officeID
where m.ID IN   (1,2,3,4,5,6)
AND p.patientID = 111
GROUP BY m.Name,  p.patientID
ORDER BY m.Name

Then a left join:

select  m.Name, p.FullName
from MedicalOffice m
left  join MedicalProcedure p ON  m.ID = p.officeID
where m.ID IN   (1,2,3,4,5,6)
AND p.patientID = 111
GROUP BY m.Name,  p.patientID
ORDER BY m.Name

but I only get the offices where a record exists in the MedicalProcedure table like this:

    Name       |    FullName
----------------------------------
Office 1         Smith, John
Office 2         Smith, John 
Office 4         Smith, John

But, if patientID 111 had 3 records in the MedicalProcedure table for Offices 1,2, and 4, the results should look like this:

Name       |    FullName
----------------------------------
Office 1         Smith, John
Office 2         Smith, John 
Office 3         NULL
Office 4         Smith, John
Office 5         NULL
Office 6         NULL

Is there a way to get the results I need?

Thanks!

Y.B. · Accepted Answer · 2016-02-10 17:18:02Z

4

Just move p.patientID = 111 condition into ON clause. In Where it effectively changes outer join into inner join:

Select
    Distinct                       --< Substitute for Group By
    m.Name, p.FullName
From MedicalOffice m
Left Join MedicalProcedure p On m.ID = p.officeID AND p.patientID = 111
Where m.ID IN   (1,2,3,4,5,6)
/* Group By m.Name, p.patientID */ --< won't work in this example
Order By m.Name

answered Feb 10, 2016 at 17:18

Y.B.

3,58617 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Dong · Accepted Answer · 2016-02-10 17:13:07Z

1

Remove

m.ID IN   (1,2,3,4,5,6)

This condition will stop null value showup

answered Feb 10, 2016 at 17:13

Tony Dong

3,3431 gold badge31 silver badges37 bronze badges

1 Comment

SkyeBoniwell Over a year ago

But I only need to show the first 6 offices(out of 73). How do I constrain that otherwise? Thanks!

Collectives™ on Stack Overflow

query to return null values when none exist in one table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related