Solved

SQL query syntax

Posted on 2014-01-16
4
206 Views
Last Modified: 2014-02-16
Hej,

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.
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39785211
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)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39785715
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?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39785741
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?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39785788
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?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question