my table looks like something like:
id | tag_id
1 | 1
2 | 2
3 | 3
1 | 2
I need a query that only grabs the id where it has all of the tag ids in the condition:
select * from table where tag_id = 1 and tag_id = 2
The above query doesn't work. I would expect ID of 1 to be returned.
I'v tried an OR but that includes results that do not have ALL of the tag_ids (1, 2) (just like OR states).
Query: mutually exclusive union
Moderator: General Moderators
-
dimxasnewfrozen
- Forum Commoner
- Posts: 84
- Joined: Fri Oct 30, 2009 1:21 pm
Re: Query: mutually exclusive union
If the id+tag_id are unique (you know there will not be duplicates) and all you need is the id (which should be true because you know the tag_ids) then
- SELECT only the id column
- WHERE to limit it to the tag_ids you want
- GROUP BY to collapse all the tag_ids down into their one respective id
- HAVING so that it only returns the ids that have two tag_ids, and since the condition only includes two tag_ids the only way this happens is if the id has both (and you can't do this in a WHERE)
Code: Select all
SELECT id FROM table WHERE tag_id IN (1, 2) GROUP BY id HAVING COUNT(*) = 2- WHERE to limit it to the tag_ids you want
- GROUP BY to collapse all the tag_ids down into their one respective id
- HAVING so that it only returns the ids that have two tag_ids, and since the condition only includes two tag_ids the only way this happens is if the id has both (and you can't do this in a WHERE)