Re: A simple join question that may stump you

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, nospam4(at)pobox(dot)com (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