Re: A simple join question that may stump you

How about:-
SELECT id
FROM (a LEFT JOIN b WHERE a.flag=b.flag)
GROUP BY id
HAVING        ((COUNT(*)=COUNT(b.flag))   AND        (COUNT(*)=(SELECT COUNT(*) FROM b AS b_cnt)));

This relys on COUNT(field) not counting NULLs, and that NULL is what the
LEFT JOIN returns for an absent b.flag:-  ID a.FLAG b.FLAG
----- ------ ------   1      1      1   2      1      1   2      2      2   3      1      1   3      2      2   3
3  NULL   4      1      1   4      3   NULL
 

N.B In your test table you missed the case of having some, but not all
of b's flags and one or more others...  ID  FLAG
----- -----   4     1   4     3


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.

-- 
This is the identity that I use for NewsGroups. Email to 
this will just sit there. If you wish to email me replace
the domain with knightpiesold . co . uk (no spaces).