CODEWITHSUNDEEP
sql
Ronen B
Ronen B

Reputation: 15

Assistance with SQL query

Suppose we have 2 tables :

  1. Patients (ID, name)
  2. Patients_Treatments (PatientID, treatment_code)

I would like the query to retrieve all the patients who received at least all the treatments of patient with id='999999999'

I've tried many combinations and nothing worked, all I've got is patients who got at least one of '9999999999' treatments.

Upvotes: 0

Views: 117

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

One method uses a self join and comparison in the having clause:

select pt2.patientId
from patient_treatments pt join
     patient_treatments pt2
     on pt.treatment_code = pt2.treatment_code and pt.patientid <> pt2.patientid
where pt.id = '999999999'
group by pt2.patientId
having count(pt2.treatment_code) = (select count(*) from patient_treatments pt where pt.id = '999999999');

Note: this version assumes that there are no duplicates in Patient_Treatments.

If you have duplicates in the data, you can use count(distinct):

having count(distinct pt2.treatment_code) = (select count(distinct pt.treatment_code) from patient_treatments pt where pt.id = '999999999');

Upvotes: 2

Tom Sharpe
Tom Sharpe

Reputation: 34180

The idea is

(1) Select treatment codes belonging to patient "999999999"

(2) Select only treatment records whose treatment code matches one of the treatment codes belonging to patient "999999999"

(3) Group these by patient_id

(4) Use a HAVING statement and COUNT (DISTINCT) to select only those Patient ID's which have the same number of different treatment codes as patient "999999999".

select pt.Patient_ID,count(distinct pt.treatment_code)
from
Patient_treatments pt
inner join
(select distinct treatment_code
 from 
 Patient_treatments pt
 where pt.Patient_ID="999999999"
 )t1
 on t1.treatment_code=pt.treatment_code
 where pt.Patient_ID<>"999999999"
 group by pt.Patient_ID
 having count(distinct pt.treatment_code)=
 (select count(distinct treatment_code) 
  from 
  Patient_treatments pt
  where pt.Patient_ID="999999999"
  );

With the following schema, only patient 1 is selected:-

  Create table Patient_Treatments
  (
    Patient_ID varchar(10),
    Treatment_code varchar(10)
    );


   Insert into Patient_Treatments
    values
    ("1","abc"),
    ("1","def"),
    ("1","def"),
    ("1","ghi"),
    ("2","abc"),
    ("2","def"),
    ("2","def"),
    ("3","ghi"),
    ("999999999","abc"),
    ("999999999","def"),
    ("999999999","ghi"),
    ("999999999","ghi")

http://sqlfiddle.com/#!9/03a84b

Upvotes: 0

Related Questions