SQL query to match multiple column values

Question

I am having trouble getting my head around the following problem.

Given the following table structure and data, how might I select records that match two tags. For example:

+-----------------+------------------+
|  collection_id  |         tag      |
+-----------------+------------------+
|        1        |    advertising   |
|        1        |     tutorials    |
|        2        |    advertising   |
|        2        |       coding     |
+-----------------+------------------+

If I search for advertising && tutorials, it should return collection_id = 1, and not collection_id = 2.

Any pointers most welcome.

What you'd like to get: scalar collection_id? Or multiple values are possible? — Andrew D.
– Andrew D., Commented Nov 14, 2012 at 8:11
I had a similar question at one point stackoverflow.com/questions/7492699/… In this case you would have a csv list of TAG's. — Gibron
– Gibron, Commented Nov 14, 2012 at 8:14

John Woo · Accepted Answer · 2012-11-14 08:11:20Z

6

SELECT collection_ID
FROM tableName
WHERE tag IN ('advertising','tutorials')
GROUP BY collection_ID
HAVING COUNT(*) = 2

SQLFiddle Demo

If unique constraint was not specified on the tag for each collection_ID

SELECT collection_ID
FROM tableName
WHERE tag IN ('advertising','tutorials')
GROUP BY collection_ID
HAVING COUNT(DISTINCT tag) = 2

SQLFiddle Demo

answered Nov 14, 2012 at 8:11

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

3 Comments

BenM Over a year ago

Thanks for your help. I copied across more data, and it seems that the query isn't returning as I'd expected. Please see here > sqlfiddle.com/#!2/021e8/3/0

John Woo Over a year ago

no, you have extra spaces on the tags see here (click here)

John Woo Over a year ago

anyone care to explain about the downvote? :D thank you. much appreciated. (no pressure if you don't want hehe)

Collectives™ on Stack Overflow

SQL query to match multiple column values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related