CODEWITHSUNDEEP
mysqlsql
BenM
BenM

Reputation: 53198

SQL query to match multiple column values

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.

Upvotes: 2

Views: 4834

Answers (1)

John Woo
John Woo

Reputation: 263703

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

Upvotes: 6

Related Questions