query to join tables and select null if the condition is not valid

Question

It's a horrible title. Let me clarify.

I'm attempting to write a query that will select certain columns from various tables which have been joined. Here's what the query looks like:

select distinct p.PAtientID, p.FirstNAme, p.LAstNAme, nrc.RiskClassification as [Risk Classification], 
ptt.NExtScheduledDAte as [Toxicology Next Scheduled Date],
ppc.NextScheduledDate as [Pill Count Next Scheduled Date],
pha.NextScheduledDate as [Mental Health Assessment Next Scheduled Date],
ppr.NExtScheduledDate as [PDMP Next Scheduled Date],
pcsa.NExtScheduledDate as [CSA Next Scheduled DAte]
from Patient p, PAtientRiskClassification prc, NetworkRiskClassification nrc, 
PAtientToxicologyTesting ptt, PAtientPillCount ppc, PAtientHEalthAssessment pha, 
PatientPRescriptionRegistry ppr, PAtientControlledSubstanceAgreement pcsa,
Prescriber pr, NetworkPRescriber np
where p.PAtientID = prc.PAtientID
and p.PAtientID = ptt.PAtientID
and ppc.PatientID = p.PAtientID
and pha.PAtientID = p.PAtientID
and ppr.PatientID = p.PAtientID
and pcsa.PatientID = p.PAtientID
and pr.PrescriberID = np.PrescriberID
and np.NetworkPrescriberID = p.NEtworkPrescriberID
and prc.NetworkRiskClassificationID = nrc.NetworkRiskClassificationID
and pr.Display like '%Kohles Brian%'

Now the problem with my query is, it only selects patient names for patients that have all of the conditions true. I wanna be able to also select patients if they have 1 of the below conditions true for them:

p.PAtientID = ptt.PAtientID
and ppc.PatientID = p.PAtientID
and pha.PAtientID = p.PAtientID
and ppr.PatientID = p.PAtientID
and pcsa.PatientID = p.PAtientID

and select null or empty string in place of columns that don't validate the condition.

Currently I'm having to write 5 separate queries like the one below:

select distinct p.PatientID, p.Display as [Patient Name], 
nrc.RiskClassification as Risk, 
ppr.NextScheduledDate as [PDMP Next Scheduled Date] from PatientPrescriptionRegistry ppr, Patient p
     cross apply
          (select  top(1) RiskClassification, PatientID from NetworkRiskClassification nrc, PatientRiskClassification prc
            where nrc.NetworkRiskClassificationID = prc.NetworkRiskCLassificationID and PatientID = p.PatientID order by PatientRiskClassificationID desc) nrc
    where p.PatientID = ppr.PatientID and
    (p.NetworkPRescriberID = 44 or p.NetworkPrescriberID = 403)
    order by p.PatientID

In short, I wanna display the "Next Scheduled Activity" column from 5 different activity tables for every patient some of whom might not have all 5 activities completed.

How can I achieve this?

*Never* use commas in the `FROM` clause. *Always* use proper, explicit `JOIN` syntax. — Gordon Linoff, Jun 16 '17 at 13:41
Yes, there are lots of reasons to not use the old, deprecated syntax of implicit joins. One of the most common ones is that you are less likely to make mistakes by forgetting join clauses. A second one is that your queries are easier to read, understand and maitnain. A third one is that it helps to understand and solve problems like you currently have. — oerkelens, Jun 16 '17 at 13:45
Read this for an explanation of why you should use the "newer" join style. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Sean Lange, Jun 16 '17 at 13:46
Thanks. I'm used to implicit joins. Seldom I use the join clause — Ahmed Mujtaba, Jun 16 '17 at 13:47
Implicit join's "outer join" syntax "*=" is no longer supported in sql server as of 2012. That would be one solid reason to break old habits. Particularly because an outer join is going to be what you need to solve your problem. — Ryan B., Jun 16 '17 at 13:51
"is there any reason for that" https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 and seldom do companies want to pay to update the code written in 89 standards to 92. However, that doesn't mean new code should be written in the 89 standard. — xQbert, Jun 16 '17 at 14:02

oerkelens · Accepted Answer · 2017-06-16T13:59:42.007

I rewrote your query (as did Sean) but I also tried to solve your problem using LEFT (OUTER) JOINs.

SELECT DISTINCT p.PAtientID
              , p.FirstNAme
              , p.LAstNAme
              , nrc.RiskClassification as [Risk Classification]
              , ptt.NExtScheduledDAte as [Toxicology Next Scheduled Date]
              , ppc.NextScheduledDate as [Pill Count Next Scheduled Date]
              , pha.NextScheduledDate as [Mental Health Assessment Next Scheduled Date]
              , ppr.NExtScheduledDate as [PDMP Next Scheduled Date]
              , pcsa.NExtScheduledDate as [CSA Next Scheduled DAte]
     FROM Patient p
     JOIN NetworkPRescriber np on np.NetworkPrescriberID = p.NEtworkPrescriberID
     JOIN Prescriber pr on pr.PrescriberID = np.PrescriberID
     JOIN NetworkRiskClassification nrc on prc.NetworkRiskClassificationID = nrc.NetworkRiskClassificationID
LEFT JOIN PAtientRiskClassification prc on prc.PatientId = p.PatientId 
LEFT JOIN PAtientToxicologyTesting ptt on ptt.PatientId = p.PatientId 
LEFT JOIN PAtientPillCount ppc on ppc.PatientId = p.PatientId 
LEFT JOIN PAtientHEalthAssessment pha on pha.PatientId = p.PatientId 
LEFT JOIN PatientPRescriptionRegistry ppr on ppr.PatientId = p.PatientId 
LEFT JOIN PAtientControlledSubstanceAgreement pcsa on pcsa.PatientId = p.PatientId 
    WHERE pr.Display like '%Kohles Brian%'

That was quick :) I hope you notice the advantage of using explicit join clauses? You only have a single real WHERE clause left :) — oerkelens, Jun 16 '17 at 14:00

score 1 · Answer 2 · answered Jun 16 '17 at 13:50

1

Try to read something about LEFT OUTER JOIN

Suppose you have two tables A and B with left outer join you will get all data from A and B where where condition is matched , in addition you will get all the records from A table that didnt match with B

answered Jun 16 '17 at 13:50

Frank

873
1
7
17

Thanks. This is what I was looking for. – Ahmed Mujtaba Jun 16 '17 at 13:56

score 1 · Answer 3 · answered Jun 16 '17 at 13:53

This would not format nicely as a comment (and it is too long). Here is your query rewritten with ANSI-92 style joins (and a little formatting in the columns). Notice how much easier this is to read. It is not a wall of text.

select distinct p.PAtientID
    , p.FirstNAme
    , p.LAstNAme
    , nrc.RiskClassification as [Risk Classification]
    , ptt.NExtScheduledDAte as [Toxicology Next Scheduled Date]
    , ppc.NextScheduledDate as [Pill Count Next Scheduled Date]
    , pha.NextScheduledDate as [Mental Health Assessment Next Scheduled Date]
    , ppr.NExtScheduledDate as [PDMP Next Scheduled Date]
    , pcsa.NExtScheduledDate as [CSA Next Scheduled DAte]
from Patient p
join PAtientRiskClassification prc on p.PAtientID = prc.PAtientID
join NetworkRiskClassification nrc on prc.NetworkRiskClassificationID = nrc.NetworkRiskClassificationID
join PAtientToxicologyTesting ptt on p.PAtientID = ptt.PAtientID
join PAtientPillCount ppc on ppc.PatientID = p.PAtientID
join PAtientHEalthAssessment on pha pha.PAtientID = p.PAtientID
join PatientPRescriptionRegistry ppr on ppr.PatientID = p.PAtientID
join PAtientControlledSubstanceAgreement pcsa on pcsa.PatientID = p.PAtientID
join Prescriber pr on pr.PrescriberID = np.PrescriberID
join NetworkPRescriber np on np.NetworkPrescriberID = p.NEtworkPrescriberID
where pr.Display like '%Kohles Brian%'

Thank you for rewriting my query, – Ahmed Mujtaba Jun 16 '17 at 13:57 — Ahmed Mujtaba, Jun 16 '17 at 13:57

score 1 · Answer 4 · answered Jun 16 '17 at 13:57

1

I am sure @sean's query works. You can join them instead of where clause. Try either inner or left join

answered Jun 16 '17 at 13:57

Ven

2,011
1
13
27

query to join tables and select null if the condition is not valid

4 Answers4