Query: mutually exclusive union

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dimxasnewfrozen
Forum Commoner
Posts: 84
Joined: Fri Oct 30, 2009 1:21 pm

Query: mutually exclusive union

Post 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).
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Query: mutually exclusive union

Post 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)
Post Reply