Arbiz Arbiz - 4 months ago 7
SQL Question

MySQL records that matches multiple criterias

There is probably a simple answer to this but I can't seem to find it anywhere..

My table looks like this:

item_id tag_id
----------------------
100 55
101 55
100 320
102 320
100 77
100 489
101 200


Asking for items with tags 55 AND 320 should return item_id = 100 (but not 101 or 102 as they don't have both tags)

Answer

You could use HAVING clause:

SELECT item_id
FROM tab_name
WHERE tag_id IN (1,2)
GROUP BY item_id
HAVING COUNT(DISTINCT tag_id) = 2;

SqlFiddleDemo

or even:

SELECT item_id
FROM tab_name
GROUP BY item_id
HAVING GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id ) = '1,2';

SqlFiddleDemo2