Get entries with all subheadings

Question

I have a table called a medicine and a related subcategory 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

A 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 information, I need to get a medicine that matches all the symptoms of the patient: (I would pull information to each patient 1 on 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 - separate table:

Symptom

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

What is a request?

Note. I made this example. In what I do, I have an A record with a set of attributes (where the attributes are stored as rows of records against the A record). I need to map this entry A to another entry C, which has the same set of attributes as A. (Making sense?)

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

Answer 1

You can combine not exists and full join … null to select all medications that do not have - not - (double negative), have a treatment for the symptoms that the patient has - so the medicine has all kinds of 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 to use conditional aggregation is to exclude any medications that are not related to the patient's symptom.

 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 full join , you can use conditional aggregation to make sure there are no null values ​​on either side of the full join, to make sure there is 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 several answers posted by FuzzyTree. Here is his first request with all the changes I had to make to get it working. His query number 2 also works.

 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 next request, which we found faster than the one that was higher (it was found by the employee and looked at the wall clock and query plans, it was faster)

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