Re: A simple join question that may stump you - Mailing list pgsql-sql

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