MySQL match against multiple values

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.

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.

Perhaps a HAVING clause will give you what you want.