SQL / MySQL: How to check "AND" logic that similar to "OR" in "WHERE IN"

Question

I have 2 tables named item and tag. They have a many-to-many relationship so their join table is item_tag as below.

-- item table
id  name
1   Item 1
2   Item 2
3   Item 3

-- tag table
id  name
1   Tag 1
2   Tag 2
3   Tag 3

-- item_tag table
item_id tag_id
1       1
2       1
2       2
3       1
3       3

I need a SQL query to get items which have both Tag 1 and Tag 2 (AND operation for given tag ids = (1, 2)).

Which means,

-- Expected output
id  name
2   Item 2

Only Item 2 has both the Tag 1 and Tag 2 so it should be AND logic for the tags.

[WHERE IN gives OR logic similarly for this scenario so cannot use it]

Can someone please help me to write that query?

Thank you!

Answer 1

get items which have both Tag 1 and Tag 2

SELECT *
FROM item
WHERE EXISTS ( SELECT NULL
               FROM item_tag 
               WHERE item.id = item_tag.item_id
                 AND tag_id = 1 )
  AND EXISTS ( SELECT NULL
               FROM item_tag 
               WHERE item.id = item_tag.item_id
                 AND tag_id = 2 )

---

when I have more tag ids, this query needs to be modified by adding the subquery for each tag ids.

The query which does not need in modifying:

SELECT item.id, item.name
FROM item
JOIN item_tag ON item.id = item_tag.item_id
WHERE item_tag.tag_id IN ( {tags list} )
GROUP BY 1,2
HAVING COUNT( DISTINCT item_tag.tag_id ) = {tags list length}

If UNIQUE index by (item_id, tag_id) exists in item_tag table structure then DISTINCT may be removed.

If you have tags names list, not ids list, then:

SELECT item.id, item.name
FROM item
JOIN item_tag ON item.id = item_tag.item_id
JOIN tag ON item_tag.tag_id = tag.id
WHERE tag,name IN ( {tag names list} )
GROUP BY 1,2
HAVING COUNT( DISTINCT item_tag.tag_id ) = {tags list length}