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