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
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.