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

user_15960967 0
Viewed 64

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!

where-in mysql sql
2 Answers

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}
user_101387340

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
user_18632290
Related