Can mySQL do this...

[frigidman]

I was pouring through mySQL docs, when i figure i might ask here as well as dig...

Say I have a table of ID to ID2:
ID....ID2
4.....24
4.....25
4.....27
5.....26
5.....24
6.....25

Now, say I want to return from the table, all "ID" that have both "ID2" of 24 AND 25. Meaning... if it doesnt have at least BOTH of those two id's, it wont bother returning it. So thus, the above would only return ID of 4.

Do a WHERE of ID2=24 and ID2=25 obviously wont work, because well... a single number cant exist as two numbers at the same time ;-) Doing an OR doesnt work either, because it then grabs ID of 4, 5 and 6 cause they have ID2 of 24 or 25.

Kind of mind numbing... I used to be able to do this, with a dynamically generated massive INTERSECT select statements, each one having a single ID2=$id... however mySQL pukes and barfs on "INTERSECT".

Ideas?

[factory21]

Just do a double join:

SELECT i1.id FROM idlink i1,idlink i2 WHERE i1.id=i2.id AND i1.id2=24 AND i2.id2=25

[frigidman]

Sorry, but that doesnt work, when there could be from 1 to possibly 1000 "id2" being used. I cant justify making a gabillion joined table for that.