Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SELECT all the records in a table that have ALL the specified characteristics from another table

to be more specific, I have a table with a list of symptoms and a table with a list of diseases. I have also created a table to link a specific symptom to a specific disease. What I’m looking to do is returning all the diseases that have ALL the selected symptoms from the symptoms’table.

Symptom

id symptom
1 Symp A
2 Symp B
3 Symp C
4 Symp D
5 Symp E

Disease

id disease
1 Dss A
2 Dss B
3 Dss C
4 Dss D

Symptom_Disease

id symp_id dss_id
1 1 2
2 1 4
3 2 2
4 2 1
5 3 1
6 4 1

So, say I select the symptoms A and B (whose ids are 1 and 2), the only disease with both those symptoms is the disease B (id 2). Is there a way to write a single select (with its joins / nested SELECTS / whatsoever) to achieve this result?

I’ve tried something like:

SELECT * 
FROM `disease` 
LEFT JOIN symptom_disease ON symptom_disease.id_disease = disease.id
LEFT JOIN symptom ON symptom.id = symptom_disease.id_symptom 
WHERE symptom_disease.id_symptom = 1 
  AND symptom_disease.id_symptom = 2;

but of course it doesn’t work.

Thank you in advance.

Tags:

3

Answers


  1. 0

    you can use a qeurie like this:

    SELECT MAX(d.id) AS ID, MAX(d.disease) AS Disease, GROUP_CONCAT(CONCAT(s.symptom,'(',s.id,')')) AS has_symptom
FROM disease AS d
LEFT JOIN sym2dss AS s2d ON d.id = s2d.dss_id
LEFT JOIN symptom AS s   ON s.id = s2d.symp_id
GROUP BY d.id
HAVING 
  SUM(s2d.symp_id IS NOT NULL) = SUM(s2d.symp_id IN(2,3,4))
AND
  SUM(s2d.symp_id IS NOT NULL) = 3;

    NOTE: You only must change the last 2 lines with this symp ids and the number of symps

    sample

    SELECT MAX(d.id) AS ID, MAX(d.disease) AS Disease, GROUP_CONCAT(CONCAT(s.symptom,'(',s.id,')')) AS has_symptom
FROM disease AS d
LEFT JOIN sym2dss AS s2d ON d.id = s2d.dss_id
LEFT JOIN symptom AS s   ON s.id = s2d.symp_id
GROUP BY d.id
HAVING 
  SUM(s2d.symp_id IS NOT NULL) = SUM(s2d.symp_id IN(2,3,4))
AND
  SUM(s2d.symp_id IS NOT NULL) = 3;
  
1   Dss A   symptom D(4),symptom C(3),symptom B(2)
    Login or Signup to reply.
  2. 0

    This answer avoids using ID’s as criteria:

    select dis.Disease, sym.Symptom
from Disease as dis
inner join Symptom_Disease as sym_dis
 on dis.ID=sym_dis.dss_ID
inner join Symptom as sym
 on sym.ID=sym_dis.Symp_ID
inner join
(select d.Disease, count(s.symptom) as CountOfSymptoms
from symptom_disease as sd
inner join Symptom as s
 on sd.Symp_ID = s.ID
inner join Disease as d
 on sd.dss_ID = d.ID
where s.Symptom = 'Symp A' or s.Symptom = 'Symp B' 
group by d.Disease
having count(s.symptom)>1) as g
 on dis.Disease=g.Disease
    Login or Signup to reply.
  3. 0

    Diseases that include both symptoms 1 and 2…

    SELECT
  dss_id
FROM
  symptom_disease
WHERE
  symp_id IN (1,2)
GROUP BY
  dss_id
HAVING
  COUNT(*) = 2

    Diseases that have exactly symptoms 1 and 2 (and no other additional symptoms)

    SELECT
  dss_id
FROM
  symptom_disease
GROUP BY
  dss_id
HAVING
  COUNT(*) = 2
  AND
  SUM(CASE WHEN symp_id IN (1,2) THEN 1 END) = 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search