SQL query to match multiple column values
mysql, sql
Solution
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
Problem
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.