Re: How to select matching row and group on it

Ashish wrote:

> 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?

My suggestion is that you post your SQL rather than asking someone to do it for you. You might also add the version number of your Oracle database too.

Daniel Morgan Received on Mon Oct 28 2002 - 10:29:31 CST