Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> How to select matching row and group on it
Hi All,
I am not able understand what query can get this thing:
T1
(C1 NUMBER,
C2 NUMBER)
C1 C2
1 1 1 2 1 3 2 4 2 5 3 6 4 7 4 8
T2
(C3 NUMBER,
C1 NUMBER ,
C2 NUMBER)
C3 C2 C1
1 1 1 1 1 2 1 2 4 1 3 6 1 4 7 2 1 3 2 3 6 2 4 8
I want to return the DISTINCT C3, which match T1.C1 AND T1.C2 such
that all distinct T1.C1 value should be present in T2 (i.e. T2 SHOULD
have atleast one record for each T1.C1) and within that C1 either of
the C2 'S matching will do.
So, in the above case it should ONLY RETURN c3 = 1
a sample query i wrote is:
select c3 from t1,t2
where t1.c1 = t2.c1
and t1.c2 = t2.c2
group by c3
having count(c1) = (select count(distinct c1) from t1)
But the query is not performing very well..
The otherone can be using INTERSECT but it does too much of sorting.
Do you have anything else to suggest? Received on Mon Oct 28 2002 - 03:59:51 CST