Search code examples
sqlsqlitemany-to-many

SQL query many-to-many: find all items with all tags included in a given set


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:

  • item1 with tags: tag1, tag2
  • item2 with tags: tag1, tag2, tag3
  • item3 with tags: tag1, tag2, tag3, tag4

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


Solution

  • 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.