Обсуждение: A simple join question that may stump you

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.

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@pobox.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

Ross,

>    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

Try looking at the INTERSECT and EXCEPT join types.  Thus, in pseudo-sql
you'd need:

SELECT A.ID
WHERE Count A JOIN B = Count B
AND Count A EXCEPT B = 0

Assuming that all rows in A are unique.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco