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.

Mark B · Accepted Answer · 2011-02-04 10:57:05Z

11

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.

answered Feb 4, 2011 at 10:57

Mark B

4,9684 gold badges27 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

duffymo · Accepted Answer · 2011-02-04 10:29:08Z

1

Perhaps a HAVING clause will give you what you want.

answered Feb 4, 2011 at 10:29

duffymo

310k46 gold badges376 silver badges570 bronze badges

3 Comments

Steven Cheng Over a year ago

I'm not quite sure how I could use the having clause to match several values.. could you provide me with an eg?

mickmackusa Over a year ago

This "hint" should have been a comment under the question.

duffymo Over a year ago

You are ten years too late. Perhaps you should use your time here more productively.

Collectives™ on Stack Overflow

MySQL match against multiple values

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related