subquery returns more than 1 row

Question

select 
    disease_name 
from 
    disease 
where 
    disease_id=
    (select disease_id from disease_symptom where
        disease.disease_id=disease_symptom.disease_id AND 
        symptom_id=
               (select symptom_id from symptom where symptom.symptom_id=disease_symptom.symptom_id
                AND symptom_name='fever' OR symptom_name='head ache'))

Gives an error that subquery returns more than one row. what is the cause?

Answer 1

Your two outer queries are structured to expect a single result from the their subqueries. But the way you have things structured, your subqueries might return more than one result. If you actually want more than one result, restructure it like this:

... where disease_id IN (subquery returning multiple rows...)

Also, subqueries is kill performance, and it's exponentially wosrse for nested subqueries. You might want to look into using INNER JOIN instead.