Page 1 of 1

Query: mutually exclusive union

Posted: Wed Apr 03, 2013 4:13 pm
by dimxasnewfrozen
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).

Re: Query: mutually exclusive union

Posted: Wed Apr 03, 2013 5:07 pm
by requinix
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

Code: Select all

SELECT id FROM table WHERE tag_id IN (1, 2) GROUP BY id HAVING COUNT(*) = 2
- 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)