SQL query syntax


MS SQL Server 2008.

I have a table of Patients and three tables of OriginalData, TreatmentData and FollowUpData.

The OriginalData, TreatmentData and FollowUpData all have foreign keys to the PatientTable.

The OriginalData, TreatmentData and FollowUpData tables also have a column called Approved.  It is of type bit.

How could I write a query that would return for each patient a row if any of the OriginalData, TreatmentData and FollowUpData tables has the Approved set to false (or null)?

So if we had three patients with Patientid of 1, 2 and 3. All patients have records in all tables.

Patient1 has a record in OriginalData with Approved=0 (with id =23) , and Patient2 has a record in FollowUpData with Approved= 0 (with id = 66)
The output I am looking for is:

Patient ID     OriginalData id   TreatmentData id   FollowUpData id
1                                        23                        
2                                                                                                       66

with no record for patient 3 because they dont have a record anywhere with approved=0

I just need the general syntax.  I though some inner joins would do if i added the condition approved=0 but then i realised i would need an "OR" between the inner joins and i guess this is not possible.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

SurranoConnect With a Mentor System EngineerCommented:
Try this:

select p.patientid, o.id as "originaldata_id", t.id as "treatmentdata_id", f.id as "followupdata_id"
from patients p 
  left join originaldata o on (p.patientid=o.patientid and coalesce(o.approved,0)=0)
  left join treatmentdata t on (p.patientid=t.patientid and coalesce(t.approved,0)=0)
  left join followupdata f on (p.patientid=f.patientid and coalesce(f.approved,0)=0);

Open in new window

(note: no sql server at hand so some trivial syntax errors may occur)
In your example, does patientid 1 exist in the treatmentdata table with an approved value of 0 (or null or false) or does it not exist in that table at all? Also, could both of those possibilities exist?
Dale FyeCommented:
Is there a 1-to-1 relationship between Patients and each of the other tables?  Or could there be multiple records in the treatmentdata and followupdata tables?
Good question, fyed, and if multiple records can exist, can some have approved = 0 and some approved = 1? If that can occur, should they be treated as approved or not?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.