AJT_82 AJT_82 - 12 days ago 4x
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:

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

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

and the intermediate table e.g:

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

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

If I change just the
clause to
it obviously isn't correct.

Thanks for any help!


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.