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

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!

CodePudding user response：

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 )

CodePudding user response：

I usually use aggregation here:

SELECT i.id, i.name
FROM item i
INNER JOIN item_tag it ON it.item_id = i.id
INNER JOIN tag t ON t.id = it.tag_id
WHERE t.name IN ('Tag 1', 'Tag 2')
GROUP BY i.id, i.name
HAVING MIN(t.id) <> MAX(t.id);