Mysql - select ids that match all tags

Question

I have a cross-reference table:

ID | tag
1  | 4
1  | 5
1  | 6
2  | 4
2  | 5
2  | 8
3  | 2

I need to select the IDs that match all of a set of tags. For example, if I were given the tags '4','5' I would get IDs '1','2'. If I were given the tags '4','2' I would not get any IDs because there were no IDs that matched all of the tags.

Also, if I were given the tags '4','9' then I also should not get any resulting IDs because a search for '9' would result in a NULL value, and therefore no IDs match all the tags.

I've been pulling my hair out for the last 2 days. Hopefully someone can help me.

Answer 1

The idea of the query is that you need to match the number of records to the number of values you have provided in the WHERE clause.

SELECT ID
FROM tableName
WHERE tag IN (4, 8)
GROUP BY ID
HAVING COUNT(*) = 2

• SQLFiddle Demo

if unique constraint was not specified on tag for every ID, then DISTINCT is needed

SELECT ID
FROM tableName
WHERE tag IN (4, 8)
GROUP BY ID
HAVING COUNT(DISTINCT tag) = 2