Hi Gentlemen

I'm a complete newbie to DB2, and using DB2 PE v7.2

I have a dead simple question for You, to which I'm unable to find an elegant solution:

I've simplified it down to

C1 C2
--- ----
a1 b1
a2 b1
a2 b2
a3 b1
a4 b1
a4 b2
a4 b3

I want to return only those C1 that cover all different values in C2, also a4. How is this done easily. in the most general form, because the original problem cover much larger tables. my play with all seems to only return empty set.

Thanks


Thanks

if you know how many different values exist upfront:
select c1
from table1
group by c1
having count(distinct c2)=4

If c2 is unique within c1, use count(*)=4
If you do not know total number of distinct c2 - calculate it first :
select max(cnt) from
(select count(distinct c2) cnt
from table1
group by c1) a

One of possible combine variants:

with tmp (c1, cnt) as
(select c1, count(distinct c2)
from table1
group by c1)
select c1
from tmp
where cnt = (select max(cnt) from tmp)