select dmp1.col_key11 col_key11,
dmp1.counter counter
from
(select dmp.col_key11 col_key11,sum(dmp.counter) counter
from
(select t1.key11 col_key11,1 counter
from t1,t2
where t1.key11=t2.key21
and t2.field2=1
and t2.key22<>'C'
union
select t1.key11 col_key11,1 counter
from t1,t2
where t1.key11=t2.key21
and t2.field2=1
and t2.key22='C') dmp
group by dmp.col_key11 ) dmp1
where dmp1.counter>1
dmp1.counter counter
from
(select dmp.col_key11 col_key11,sum(dmp.counter) counter
from
(select t1.key11 col_key11,1 counter
from t1,t2
where t1.key11=t2.key21
and t2.field2=1
and t2.key22<>'C'
union
select t1.key11 col_key11,1 counter
from t1,t2
where t1.key11=t2.key21
and t2.field2=1
and t2.key22='C') dmp
group by dmp.col_key11 ) dmp1
where dmp1.counter>1
dmp1.counter counter
from
(select dmp.col_key11 col_key11,sum(dmp.counter) counter
from
(select distinct t1.key11 col_key11,1 counter
from t1,t2
where t1.key11=t2.key21
and t2.field2=1
and t2.key22<>'C'
union
select distinct t1.key11 col_key11,1 counter
from t1,t2
where t1.key11=t2.key21
and t2.field2=1
and t2.key22='C') dmp
group by dmp.col_key11 ) dmp1
where dmp1.counter>1
key11
from t1, t2
where key11=key21
and key22<>'C'
and t2.field2='1'
;