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:
>
>
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 cou
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
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:-
