Get records that have all sub records

Question

I have a table called medicine and an associated sub table called Medication_symptoms

Medicine

MediId, Name
1,      MedA
2,      MedB
3,      MedC

Medication_symptoms

MedSympId, Medicine (MedId),     Symptom (symptomId)
1,         MedA (1),             Symptom A (1)
2,         MedA (1),             Symptom B (2)
3,         MedB (2),             Symptom B (2)
4,         MedB (2),             Symptom C (3)
5,         MedC (3),             Symptom D

I have another table called Patient and Patient_Symptoms

Patient

PatientId, Name
1,         Patient A
2,         Patient B
3,         Patient C
4,         Patient D

Patient_Symptom

PatientSymptomId, PatientId,     SymptomId
1,                Patient A(1),  Symptom A (1)
2,                Patient A(1),  Symptom B (2)
3,                Patient B(2),  Symptom B (2)
4,                Patient B(2),  Symptom D (4)
5,                Patient D(4),  Symptom D (4)

Given the above info, I need to get the medication that matches all symptoms of a patient: (I would be pulling the info by each patient 1 by 1)

Patient A - Med A (as he has symptom a and b and Med is for symptom A and B)
Patient B - None! (as he has symptoms b and d and there is no medicine for symptoms B and D)
Patient D - Med C (as med C is for symptom D only and Patient D has only symptom D)

Note Symptoms is a separate table:

Symptom

Symptom Id, Name
1,          Symptom A
2,          Symptom B
3,          Symptom C
4,          Symptom D
5,          Symptom E

What is such a query called?

Note: I have made up this example. In what I am doing I have a record A, with a set of attributes (where the attributes are stored as rows of records against Record A). I need to match that record A with another record C, that has the exact same set of attributes as A. (Making sense?)

You can create the tables and some sample data using the script at http://pastebin.com/kaqdtHf3

Answer 1

You can combine not exists and full join … null to select all medicines that do not - not - (double negative) have a treatment for symptom that a patient has - therefore the medicine does have all treatments

select * from medicine m
where not exists (
    select 1 from patient_symptom ps 
    full join medication_symptoms ms on ps.SymptomId = ms.SymptomId
          and ps.PatientId = :myPatientIdHere
          and ms.MedId = m.MedId
    where (ms.SymptomId is null or ps.symptomId is null)
)

Another way using conditional aggregation to exclude any medicines that do not treat a symptom of a patient

select ms.MedId
from patient_symptom ps
join medication_symptoms ms on ps.SymptomId = ms.SymptomId
where ps.patientId = :myPatientIdHere
group by ms.MedId, ps.patientId
having count(ms.symptomId) = (select count(*) from patient_symptom ps2 
                                where ps2.patientId = ps.patientId)
and count(ms.symptomId) = (select count(*) from medication_symptoms ms2 
                                where ms2.MedId = ms.MedId)

Update

If you use a full join, you can use conditional aggregation to make sure that there are no null values on either side of the full join to make sure there's a 1:1 match.

select t1.MedId
from (
  select * from 
  patient_symptom ps
  cross join medicine m
  where patientId = :myPatientId
) t1
full join medication_symptoms ms on t1.SymptomId = ms.SymptomId
      and t1.MediId = ms.MediId
group by t1.MedId
having count(case when t1.SymptomId is null or ms.SymptomId is null then 1 end) = 0

Answer 2

There are multiple answers that FuzzyTree posted. Here is his first query with all the changes that I had to make to get it to work. His query #2 works too.

SELECT *
FROM medicine m
WHERE NOT EXISTS (
        SELECT 1
        FROM (
            SELECT ms.symptomId
            FROM Medication_Symptoms ms
            WHERE ms.medId = m.medid
            ) ms1
        FULL JOIN (
            SELECT ps.SymptomId
            FROM Patient_Symptom ps
            WHERE ps.PatientId = 7
            ) ps1 ON ps1.SymptomId = ms1.SymptomId
        WHERE (
                ps1.SymptomId IS NULL
                OR ms1.symptomId IS NULL
                )
        )

The following query we have found to be more faster than the one above (this was found by a co-worker and looking at wall clock times and the query plans, this one was quicker)

select m.Name
from Medicine m
where m.Id in (
    select ms.MedicineId
    from       Medication_Symptom ms
    inner join (select SymptomId
        from Patient_Symptom
        where PatientId = 7)      ps on ps.SymptomId = ms.SymptomId
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = 7)
    intersect
    select ms.MedicineId
    from Medication_Symptom ms
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = 7)
    )

finally, this query returns the data for all patients:

select po.Name, m.Name
from Medicine m, patient po
where m.Id in (
    select ms.MedicineId
    from       Medication_Symptom ms
    inner join (select SymptomId
        from Patient_Symptom
        where PatientId = po.Id)      ps on ps.SymptomId = ms.SymptomId
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = po.Id)
    intersect
    select ms.MedicineId
    from Medication_Symptom ms
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = po.Id)
    )