Multi column subquery help requested

IDkey| ColA| ColB| ColC
100| A12693| 1528602| 132.56
101| A12693| 1528602| 132.56
200| A13823| 363985| 52.12
201| A13823| 363985| 52.12
300| A23234| 435675| 12.14
To pull out the dups, I can use:
select cola, colb, colc, count(*)
from table
group by cola, colb, colc
having count(*)>1
I want to get the IDkey associated with them, but dont know how.
Conceptually I want to do
select idkey
from table
where
(
select cola, colb, colc, count(*)
from table
group by cola, colb, colc
having count(*)>1
)
Any help is greatly appreciated.I havn't tested it yet, but i would try something like:
select idkey, cola, colb, colc
from table
INNER JOIN (SELECT cola, colb, colc
group by cola, colb, colc
having count(*)>1) AS a
ON (a.cola = table.cola AND a.colb = table.colb AND a.colx = table.colc)
Hope this helps you.
--
Elmar Jansen
http://www.learnit.nl
"SandpointGuy" wrote:
> IDkey| ColA| ColB| ColC
> 100| A12693| 1528602| 132.56
> 101| A12693| 1528602| 132.56
> 200| A13823| 363985| 52.12
> 201| A13823| 363985| 52.12
> 300| A23234| 435675| 12.14
> To pull out the dups, I can use:
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> I want to get the IDkey associated with them, but dont know how.
> Conceptually I want to do
> select idkey
> from table
> where
> (
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> )
> Any help is greatly appreciated.|||A typo, of course "a.colx = table.colc" should be "a.colc = table.colc".
So:
select idkey, cola, colb, colc
from table
INNER JOIN (SELECT cola, colb, colc
group by cola, colb, colc
having count(*)>1) AS a
ON (a.cola = table.cola AND a.colb = table.colb AND a.colc = table.colc)|||Almost. Add an exists and correlate:
select idkey
from table T1
where EXISTS
( select cola, colb, colc
from table T2
where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
group by cola, colb, colc
having count(*)>1
)
You can also skip the superfluous group, count and having completely with
the following simple formulation
select idkey from table T1
where exists
(select * from table T2
where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
and T1.idkey <> T2.idkey)
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:C3FC8DBD-ED40-4BC6-BEB5-6BA416B623E7@.microsoft.com...
> IDkey| ColA| ColB| ColC
> 100| A12693| 1528602| 132.56
> 101| A12693| 1528602| 132.56
> 200| A13823| 363985| 52.12
> 201| A13823| 363985| 52.12
> 300| A23234| 435675| 12.14
> To pull out the dups, I can use:
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> I want to get the IDkey associated with them, but dont know how.
> Conceptually I want to do
> select idkey
> from table
> where
> (
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> )
> Any help is greatly appreciated.|||Thanks much!
"Mark Yudkin" wrote:
> Almost. Add an exists and correlate:
> select idkey
> from table T1
> where EXISTS
> ( select cola, colb, colc
> from table T2
> where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
> group by cola, colb, colc
> having count(*)>1
> )
> You can also skip the superfluous group, count and having completely with
> the following simple formulation
> select idkey from table T1
> where exists
> (select * from table T2
> where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
> and T1.idkey <> T2.idkey)
>
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:C3FC8DBD-ED40-4BC6-BEB5-6BA416B623E7@.microsoft.com...
> > IDkey| ColA| ColB| ColC
> > 100| A12693| 1528602| 132.56
> > 101| A12693| 1528602| 132.56
> > 200| A13823| 363985| 52.12
> > 201| A13823| 363985| 52.12
> > 300| A23234| 435675| 12.14
> >
> > To pull out the dups, I can use:
> > select cola, colb, colc, count(*)
> > from table
> > group by cola, colb, colc
> > having count(*)>1
> >
> > I want to get the IDkey associated with them, but dont know how.
> > Conceptually I want to do
> >
> > select idkey
> > from table
> > where
> > (
> > select cola, colb, colc, count(*)
> > from table
> > group by cola, colb, colc
> > having count(*)>1
> > )
> >
> > Any help is greatly appreciated.
>
>