Solved

SQL query syntax

Posted on 2014-01-16
4
198 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 500 total points
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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)
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now