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!

Yes the combination of (item_id, tag_id) should be unique in this case — Mihiran Paranamana, Aug 26 '22 at 06:44
I don't ask should it be unique or not. I ask does unique index by these columns exists in the table structure. If not then these columns combination may contain duplicates, and nothing prevents this. — Akina, Aug 26 '22 at 06:50
@MihiranParanamana do you know the tag ids you need to check? like in above example 1 & 2 — Kalhan.Toress, Aug 26 '22 at 07:32

Akina · Accepted Answer · 2022-08-26T06:57:46.960

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}

Thank you for your answer. But when I have more tag ids, this query needs to be modified by adding the subquery for each tag ids. isn't it? — Mihiran Paranamana, Aug 26 '22 at 06:48
@TimBiegeleisen Visually true, but it is reminiscent only. `MIN(t.id) <> MAX(t.id)` works with 2-entry list only. — Akina, Aug 26 '22 at 08:27

Tim Biegeleisen · Answer 2 · 2022-08-26T07:02:32.467

2

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);

Note: Use the following HAVING clause to support an arbitrary number of tags:

HAVING(COUNT DISTINCT t.id) = 2  -- replace 2 by number of tags

edited Aug 26 '22 at 07:02

answered Aug 26 '22 at 05:58

Tim Biegeleisen

502,043
27
286
360

When I add 'Tag 3' as well into the IN () part, it will return 'Item 2' and 'Item 3'. But it should not return any, because there is no item here which has all the tags. Any idea why is that? – Mihiran Paranamana Aug 26 '22 at 07:00
Check my updated answer. You can use the alternative `HAVING` clause. – Tim Biegeleisen Aug 26 '22 at 07:03

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

2 Answers2