Subqueries & Agrigate queries

[kugaflak]

Hi

Im certanly no expert at SQL and I was wondering if some one could help me out with a query.

Here is a simplified description of the issue:

I have three tables one is Patient another is Medication and the Last is MedicationChange...

Patient
=======================================
Patient_id(PK) Name Address
=======================================

Medication
=====================================================
Medication_id(PK) Patient_id(FK) Medication
=====================================================

MedicationChange
========================================================
MedicationChange_id(PK) Medication_id(FK) Reason

Dose Dose_Date
========================================================

They are all related together. The relationship is expressed as 1 Patient to many Medications and 1 Medication to many MedicationChanges.

What I wish to extract from the data is any Patient that is on a certain Medication eg:antibiotics AND is on another Medication eg:steriods but has two MedicationChange records related with it both stating the Reason for change is 'Illergic reaction'.

I realise that a query & subquery (Using IN) will get any patient on two medications but im having trouble in trying to express that the second medication must have two related MedicationChange records that both state in reason field 'Illergic reaction'.

Thanks in advance for anyone that can point me in the right direction

 

[PHV]

Something like this ?
SELECT P.*
FROM (((Patient AS P
INNER JOIN Medication AS M1 ON P.Patient_id = M1.Patient_id)
INNER JOIN MedicationChange C1 ON M1.Medication_id = C1.Medication_id)
INNER JOIN Medication AS M2 ON P.Patient_id = M2.Patient_id)
INNER JOIN MedicationChange C2 ON M2.Medication_id = C2.Medication_id
WHERE M1.Medication = 'antibiotics' AND C1.Reason = 'Illergic reaction'
AND M2.Medication = 'steriods' AND C2.Reason = 'Illergic reaction'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886