Re: to all SQL experts...

From: Martin Haltmayer <Martin.Haltmayer_at_d2mail.de>
Date: Mon, 26 Aug 2002 09:22:50 +0200
Message-ID: <3D69D74A.16A006A8@d2mail.de>

Assumed you have (col1, col2) as a primary key on your testtab the following worked for me:

select distinct dt1.col1, dt2.col1
from
(
select t1.col1, count (*) as num_col2
from testtab t1
group by t1.col1
) dt1
,
(
select t1.col1 as col1a, count (t2.col1) as num_col1b from testtab t1, testtab t2
where t1.col1 != t2.col1
and t1.col2 = t2.col2
group by t1.col1
) da1
,
(
select count (t1.col1) as num_col1a, t2.col1 as col1b from testtab t1, testtab t2
where t1.col1 != t2.col1
and t1.col2 = t2.col2
group by t2.col1
) db1
,
(
select t1.col1, count (*) as num_col2
from testtab t1
group by t1.col1
) dt2
where dt1.col1 = da1.col1a

and da1.num_col1b = db1.num_col1a
and db1.col1b = dt2.col1
and dt1.col1 < dt2.col1

order by 1, 2
/

Regards,

Martin

Chris Aiello wrote:
>
> I am stumped, although that's not really hard....
>
> Here is my predicament:
>
> I have a table that has 2 columns, column 1 repeats values and column 2 can
> have any value. Here's an example
>
> col1 col2
> 1 15
> 1 35
> 1 46
> 1 72
> 2 10
> 2 35
> 2 46
> 2 11
> 2 19
> 2 22
> 3 15
> 3 35
> 3 46
> 3 72
> 4 11
> 4 25
>
> Here's the dilemna...
> I need a query that can find distinct col1 values that have matching col2
> entries, along with the same number of entries. For example col1 value '1'
> has 4 entries with '15','35','46',and '72'. col1 value 2 has the entries
> 10,35,46,11,19 and 22. value 3 has 15,35,46, and 72. col1 values 1 and 3
> have identical corresponding entries...the same values and the same number
> of entries. Can anyone help me out with this??
> thanks, Chris.
Received on Mon Aug 26 2002 - 02:22:50 CDT