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])

Reply via email to