MySQL match against multiple values

Question

I've got the following table:

nid | tag_id
--------------
1   | 213
1   | 78
2   | 938
2   | 1002
2   | 8573
2   | 5
3   | 3957
3   | 487
4   | 56

I want to retreive a single nid where tag_id matches several values say 1002,938,8573.

I started with the following query:

SELECT nid,GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id) tag_ids FROM table GROUP BY nid

which returns:

nid | tag_ids
--------------
1   | 78,213
2   | 5,938,1002,8573
3   | 487,3957
4   | 56

But I haven't found anything yet that'll will allow me to match the tag_ids column again my set of values. I need it to match all not just anyone of the values.

Maybe my approach is wrong so happy to look at different methods.

Answer 1

Assuming you are building your query in some sort of application code, you could use

SELECT nid
FROM table
WHERE tag_id IN (tag1, tag2, tag3, ...)
GROUP BY nid
HAVING COUNT(*) = n;

where n is the number of tags in your list. This should find all nids which match your entire tag list.

Answer 2

Perhaps a HAVING clause will give you what you want.