Re: How to select matching row and group on it

From: David Fitzjarrell <oratune_at_msn.com>
Date: 28 Oct 2002 12:07:23 -0800
Message-ID: <32d39fb1.0210281207.18990fed@posting.google.com>

As written you're query doesn't work:

13:53:43 TECHDB> select c3 from t1,t2
13:53:56   2  where t1.c1 = t2.c1
13:53:56   3  and   t1.c2 = t2.c2
13:53:56   4  group by c3
13:53:56   5  having count(c1) = (select count(distinct c1) from t1)
13:53:56   6  /
having count(c1) = (select count(distinct c1) from t1)
             *

ERROR at line 5:
ORA-00918: column ambiguously defined

Modified it doesn't return any records:

13:54:29 TECHDB> l
  1 select c3 from t1,t2
  2 where t1.c1 = t2.c1
  3 and t1.c2 = t2.c2
  4 group by c3
  5* having count(t1.c1) = (select count(distinct c1) from t1) 13:56:32 TECHDB> / no rows selected

13:56:33 TECHDB> Since count(distinct c1) from t1 returns 4, and since none of your entries has a count of 4 I can see why no records are returned. Eliminating the group by and having clauses returns the desired result:

14:06:01 TECHDB> l
  1 select c3 from t1,t2
  2 where t1.c1 = t2.c1
  3* and t1.c2 = t2.c2
14:06:04 TECHDB> /         C3

So WHY go through all of the unnecessary 'hoops' that lead you nowhere?

ashishdhoke_at_hotmail.com (Ashish) wrote in message news:<9d32614.0210280159.60e34cbe_at_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 - 14:07:23 CST