I'm working with SQLite. Basically, I have a many-to-many relationship between items and tags. I want to find every item such that all its tags are included in a given set (found by another query). So if I have an item with tag1, tag2 and tag3 and the set is only tag1 and tag2, I should not get that item.
I've tried several different approaches, the latest being on the lines of selecting item_id
such that COUNT(item_id)
in table Items_Tags
is equal to COUNT(item_id)
with the condition tag IN (tag1, tag2)
(for example). Of course, I'd need to count only the appearances of the current item_id
and I can't think of a way to do that:
SELECT i.item_id, i.item_name FROM Items i
JOIN Items_Tags it
ON i.item_id = it.item_id
WHERE i.item_id IN (
SELECT item_id FROM Items_Tags
GROUP BY item_id
HAVING COUNT(item_id) = (
SELECT COUNT(item_id) FROM Items_Tags
WHERE item_id = current_item_id???
AND tag IN (tag1, tag2)
)
)
I'm sure I'm not tackling this problem in the best possible way since I'm not used to working with SQL. Any help would be greatly appreciated.
EDIT: To make myself more clear, if there are three items:
And the given set of tags is tag1, tag2 and tag3, I want item1 and item2 to be returned (as all their tags are included in the set), but not item3
The answer by forpas got me thinking and I came up with another perspective to my problem: I want every item whose tags are all included in a set (from another query), so I actually want all items that are not included among those having a tag that is not included in the set. So:
SELECT DISTINCT i.item_id, i.item_name FROM Items i
JOIN Items_Tags it
ON i.item_id = it.item_id
WHERE i.item_id NOT IN (
SELECT item_id FROM Items_Tags
WHERE tag NOT IN (list_of_tags)
)
I will welcome any comments and other answers before accepting this one, in case I'm missing something.