This is one way that comes up: select id from ( select distinct a.id AS id , b.flag AS flag from A, B where a.flag = b.flag ) a_distinct where id not in (select id from a where flag not in (select flag from b)) group by id having count(*) = (select count(*) from b) ;
Arian. On 25 Sep 2001 20:01:06 -0700, [EMAIL PROTECTED] (Ross Smith) wrote: >OK, I have 2 tables, table A: > > ID FLAG >----- ----- > 1 1 > 2 1 > 2 2 > 3 1 > 3 2 > 3 3 > >and table B: > > FLAG >----- > 1 > 2 > >I want to find all id's from table A that have every flag in table B >but no extra flags. So, I'd end up with: > > ID >----- > 2 > >As id 2 has both flag 1 and flag 2, id 1 doesn't have flag 2, and id 3 >has flag 3. > >I know it can be done, 'cause I've done it in the past, but I've spent >hours on this to no avail. Surfing the net proved fruitless as well. > >Any help would be greatly appreciated. Arian Prins / Rock Resort --U-N-L-E-A-S-H-E-D-- (keyboards/production/songwriting) listen at: http://www.mp3.com/RockResort ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])