2

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
BenM
  • 52,573
  • 26
  • 113
  • 168
  • What you'd like to get: scalar `collection_id`? Or multiple values are possible? – Andrew D. Nov 14 '12 at 08:11
  • I had a similar question at one point http://stackoverflow.com/questions/7492699/how-can-i-structure-a-query-to-give-me-only-the-rows-that-match-all-values-in-a In this case you would have a csv list of TAG's. – Gibron Nov 14 '12 at 08:14

1 Answers1

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

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
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 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 > http://sqlfiddle.com/#!2/021e8/3/0 – BenM Nov 14 '12 at 08:18
  • no, you have extra spaces on the tags [see here (*click here*)](http://sqlfiddle.com/#!2/021e8/9) – John Woo Nov 14 '12 at 08:19
  • anyone care to explain about the downvote? :D thank you. much appreciated. (*no pressure if you don't want hehe*) – John Woo Nov 14 '12 at 08:22