CODEWITHSUNDEEP
mysqlselectgrouping
WindBridges
WindBridges

Reputation: 753

MySQL: Select items that belong to all listed categories

(MySQL) Please help to select all items, that present in ALL categories.

Structure: item_category (item_id, category_id)
1 1
1 2
1 3
2 1
2 2

This query:

SELECT item_id FROM item_category WHERE category_id IN (1,2,3)

selects all item ids that belongs to categories 1 OR 2 OR 3 (returns ids 1, 2). How should I select items that exists in categories 1 AND 2 AND 3 (to return only id 1)?

Upvotes: 0

Views: 2124

Answers (1)

juergen d
juergen d

Reputation: 204756

To get items having all categories use

SELECT item_id 
FROM item_category 
group by item_id 
having count(distinct category_id) = (select count(distinct category_id) 
                                      from item_category)

or if you know there are always those 3 category_ids use

SELECT item_id 
FROM item_category 
group by item_id 
having count(distinct category_id) = 3

or if you have many category_ids in your table but want only items having at least the categories 1,2,3 then use

SELECT item_id 
FROM item_category 
where category_id in (1,2,3)
group by item_id 
having count(distinct category_id) = 3

Upvotes: 3

Related Questions