Choose in MySQL based on two tables

Question

I have two tables.

diseases

-----------------------------
| ID  |  NAME               |
-----------------------------
| 1   | Disease 1           |
| 2   | Disease 2           |
| 3   | Disease 3           |

diseases_symptoms

-----------------------------
| DISEASE_ID  | SYMPTOM_ID  |
-----------------------------
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 1           | 4           |
| 2           | 1           |
| 2           | 2           |

I want to select all diseses that have symptoms 1 or 2 and 3 or 4.

I tried:

SELECT * 
 FROM diseases_symtoms 
WHERE (symptoms = '1' OR symptoms = '2') 
  AND (symptoms = '3' OR symptoms = '4')

and

SELECT * 
  FROM diseases_symtoms 
  WHERE symptoms IN ('1','2') 
    AND symptoms IN ('3','4')

... but it does not work.

Answer 1

Keep in mind that SELECT can only look at one row at a time. Both of these queries act as if you can simultaneously detect 1a 3(for example), which is impossible.

To consider several rows at once, you can either join two separate copies of the table, or try this grouping:

SELECT diseases.*
FROM diseases
INNER JOIN diseases_symptoms ON (disases_symptoms.disease_id = diseases.disease_id)
GROUP BY diseases.disease_id
HAVING SUM(IF(symptoms = 1 OR symptoms = 2, 1, 0) > 0 AND SUM(IF(symptoms = 3 OR symptoms = 4, 1, 0) > 0

Answer 2

SELECT d.* FROM diseases AS d
INNER JOIN disease_symptoms AS s1 ON s1.DISEASE_ID = d.ID WHERE SYMPTOM_ID IN (1, 2)
INNER JOIN disease_symptoms AS s2 ON s2.DISEASE_ID = d.ID WHERE SYMPTOM_ID IN (3, 4)
GROUP BY d.ID

Answer 3

...

SELECT DISTINCT *
    FROM diseases
    WHERE EXISTS (SELECT *
                       FROM disease_symptoms
                       WHERE disease.disease_id = disease_symptoms.disease_id AND
                             symptom_id IN (1,2)) AND
          EXISTS (SELECT *
                       FROM disease_symptoms
                       WHERE disease.disease_id = disease_symptoms.disease_id AND
                             symptom_id IN (3,4));