ToraSonic ToraSonic - 6 months ago 12
MySQL Question

Select only ids with tags that matches an array exactly

Lets say I have a table of ids and tags

id | tags
1 | apples
1 | pears
2 | apples
2 | pears
2 | oranges
3 | apples
3 | pears
3 | oranges
3 | lemons


I want to query only for ids that have the tags ('apples', 'pears', oranges').
So the desired output in this case is only id 2.

After reading other similar posts, I have tried the following query statement in mysql:

SELECT
id
FROM table
WHERE tags IN ('apples', 'pears', 'oranges')
GROUP BY id
HAVING COUNT(DISTINCT tags)=3;


But this returns both id 2 & 3, and I only want id 2 in my case.

Answer

Use a CASE expression in the HAVING clause:

SELECT id 
FROM t
GROUP BY id 
HAVING 
    COUNT(DISTINCT CASE WHEN tags IN('apples', 'pears', 'oranges') THEN tags END)=3
    AND COUNT(DISTINCT CASE WHEN tags NOT IN('apples', 'pears', 'oranges') THEN tags END) = 0;

ONLINE DEMO