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?
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.
Breaking your query down, you have
Main query:
select disease_name from disease where disease_id=
Subquery 1:
select disease_id from disease_symptom where
disease.disease_id=disease_symptom.disease_id AND
symptom_id=
Sub query 2:
select symptom_id from symptom where symptom.symptom_id=disease_symptom.symptom_id
AND symptom_name='fever' OR symptom_name='head ache'
Since you are using equal signs, the subqueries cannot return multiple items. It looks like sub query 2 has a greater chance to return 2 items due to the OR
being used. You may wish to try IN
clause such as WHERE symptom_id IN (sub-query2)
with WHERE disease_id IN (sub-query1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With