Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL query syntax

Posted on 2014-01-16
4
Medium Priority
?
212 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
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
Surrano earned 2000 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 49

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

886 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