AJT_82 AJT_82 - 1 month ago 5
SQL Question

SQL SELECT (many-2-many)

I tried to browse through previously asked question for my problem, but there are SO many questions. Hopefully this is not a duplicate I'm new to SQL, so still trying to wrap my head around this! BTW, I'm using MySQL.

So I have 2 tables:

Sickness
id: 1, name: flu
id: 2, name: migraine

Symptom
id: 1, name: cough
id: 2, name: headache


and the intermediate table e.g:

Sickness2Symptom
sickness_id: 1, symptom_id: 1
sickness_id: 1, symptom_id: 2
sickness_id: 2, symptom_id: 2


I would like a query that lists all sicknesses that do not have a relation to a specific symptom.

So if we look at retrieving all created relations between a symptom and sicknesses. Let's say the symptom has
id=1 (cough)
. That query would look like:

SELECT sickness.*
FROM sickness
JOIN sickness2symptom
ON sickness2symptom.sickness_id = sickness.id
JOIN symptom
ON symptom.id = sickness2symptom.symptom_id
WHERE symptom.id = 1";


So that would return
"flu"
.

But how would I query to get all sicknesses that do not have a relation to symptom with
id 1
, in this case the result of the query would be
"migraine"
?

If I change just the
WHERE
clause to
!=1
it obviously isn't correct.

Thanks for any help!

Answer

Here is one method:

select s.*
from sickness s
where not exists (select 1
                  from Sickness2Symptom s2s
                  where s2s.sickness_id = s.id and s2s.symptom_id = 1
                 );

Note that you don't actually need the symptom table, because the information you need is in Sickness2Symptom.

Also, a not exists query can also be expressed as NOT IN:

where s.id NOT IN (select s2s.sickness_id
                   from Sickness2Symptom s2s
                   where s2s.symptom_id = 1
                  );

Or as LEFT JOIN with a WHERE clause.

Comments