Solved

SQL query syntax

Posted on 2014-01-16
4
204 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

735 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