How to return records which don't have a match in a tag relation table in MySQL?-CodePudding

Given the tag (ie: "animal"), how can I return the records from the objects table which don't have the tag "animal"?

Objects Table:

object_id	object_name
1	cat
2	dog
3	truck
4	car

Tags Table:

tag_id	tag
1	animal
2	vehicle
3	red

Object Tags Table:

tag_id	object_id
1	1
1	2
3	2
2	3
2	4
3	4

I'm doing something like this. However, 'dog' is still returning because it has a matching tag for 'red'.

SELECT o.* 
FROM objects o
LEFT JOIN object_tags ot
   ON ot.object_id = o.object_id
LEFT JOIN tags t
   ON ot.tag_id = t.tag_id  
   AND LOWER(t.tag) = LOWER('animal') 
WHERE t.label IS NULL 
GROUP BY o.object_id

CodePudding user response：

I'm doing something like this. However, 'dog' is still returning because it has a matching tag for 'red'.

Based on the data example the only excluded record should be object_id=1

You could use NOT EXISTS which will exclude the records which have the tag='animal' :

select o.*
from objects o 
where not exists (select 1 from object_tags ot
                  inner join tags t on ot.tag_id=t.tag_id
                  where o.object_id=ot.object_id
                  and t.tag='animal'
                  );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b96e6ab628cb2347838c2e8f253d0475