Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How to select matching row and group on it

How to select matching row and group on it

From: Ashish <ashishdhoke_at_hotmail.com>
Date: 28 Oct 2002 01:59:51 -0800
Message-ID: <9d32614.0210280159.60e34cbe@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US