I have a table with objects, a table with tags and a linking table.
The statement without the second condition works, but the out commented one yields no results, this probably because objects_tags.id_tag
cannot be two different numbers at the same time.
x
SELECT objects.id
FROM objects
JOIN objects_tags ON objects.id = objects_tags.id_object
JOIN tags ON tags.id = objects_tags.id_tag
WHERE objects_tags.id_ IN (3,4);
#WHERE (objects_tags.id_tag IN (3,4)) AND (objects_tags.id_tag = 6);
Is there a way to select all the entries in objects
which are linked to tags
with ID 3 or 4 and 6?
Advertisement
Answer
I would recommend group by
and having
:
SELECT ot.id_object
FROM objects_tags ot
GROUP BY ot.id_object
HAVING SUM(ot.id_ IN (3, 4)) > 0 AND -- has either 3 or 4
SUM(ot.id_tag = 6) > 0; -- also has 6
Note that you only need the object_tags
table for this query, because the query is only using ids. If you want additional information, then join
s may be necessary.