Select 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 which have symptoms 1 or 2 and 3 or 4.

I've 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 is not working.

Answer 1

Keep in mind that SELECT can only examine one row at a time. Both those queries act as if you can detect a 1 and a 3 simultaneously (for example), which is not possible.

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

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