multi column subquery using IN clause

Is there a way to do a multi column subquery using a IN clause?
Example of what works for Oracle and needs to get it to work for SQL Server
2000:
SELECT *
FROM Test T
WHERE (T.Key1, T.Key2) IN
(SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
NULL)
I would like to keep this a prepared statement if possible.
Thanks in advance, KevinUse EXISTS:
SELECT *
FROM Test T
WHERE EXISTS
(SELECT *
FROM SubSelect S
WHERE S.End_Date IS NULL
AND S.Key1 = T.Key1
AND S.Key2 = T.Key2)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in message
news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
> Is there a way to do a multi column subquery using a IN clause?
> Example of what works for Oracle and needs to get it to work for SQL
> Server
> 2000:
> SELECT *
> FROM Test T
> WHERE (T.Key1, T.Key2) IN
> (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
> NULL)
> I would like to keep this a prepared statement if possible.
> Thanks in advance, Kevin|||n columns = n conditions
:)
Try using this function (several times in the same query in as many joins as
you need):
http://solidqualitylearning.com/Blo.../10/22/200.aspx
E.g. four columns => four variables => four instances of the function in
four joins
ML|||Why not just do a JOIN?
SELECT T.<col1>, T.<col2>, ...
FROM Test T
INNER JOIN SubSelect S
ON T.Key1 = S.Key1
AND T.Key2 = S.Key2
WHERE S.End_Date IS NULL
Tom's EXISTS works also, but the above truly represents what you're doing...
seems to me the most intuitive approach to the query. Try to avoid SELECT *
in production code, FWIW.
"Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in message
news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
> Is there a way to do a multi column subquery using a IN clause?
> Example of what works for Oracle and needs to get it to work for SQL
> Server
> 2000:
> SELECT *
> FROM Test T
> WHERE (T.Key1, T.Key2) IN
> (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
> NULL)
> I would like to keep this a prepared statement if possible.
> Thanks in advance, Kevin|||On Mon, 31 Oct 2005 12:23:19 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>Why not just do a JOIN?
>SELECT T.<col1>, T.<col2>, ...
> FROM Test T
> INNER JOIN SubSelect S
> ON T.Key1 = S.Key1
> AND T.Key2 = S.Key2
> WHERE S.End_Date IS NULL
>Tom's EXISTS works also, but the above truly represents what you're doing..
.
>seems to me the most intuitive approach to the query.
Hi Aaron,
If the relationship between Test and Subselect is one to many (with
Subselect being the many), the join will introduce duplicates that are
avoided with Tom's EXISTS.

>Try to avoid SELECT *
>in production code, FWIW.
Amen to that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> If the relationship between Test and Subselect is one to many (with
> Subselect being the many), the join will introduce duplicates that are
> avoided with Tom's EXISTS.
True. When given crappy specs, we have to make some assumptions. If we ask
more information, we're being pesky. Or so I'm told.|||Your heart is in the right place and that is legal SQL-92 syntax. But
SQL Server is behidn the curve.
Use an EXISTS() predicate and watch out for NULLs.|||Tom,
The EXISTS works and is generic to work with both Oracle and SQL Server.
Thanks for everyone's help.
"Tom Moreau" wrote:

> Use EXISTS:
> SELECT *
> FROM Test T
> WHERE EXISTS
> (SELECT *
> FROM SubSelect S
> WHERE S.End_Date IS NULL
> AND S.Key1 = T.Key1
> AND S.Key2 = T.Key2)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in messa
ge
> news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
>
>|||On Mon, 31 Oct 2005 21:46:07 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>True. When given crappy specs, we have to make some assumptions. If we as
k
>more information, we're being pesky. Or so I'm told.
>
Aye, it's a thin line we're treading. And we can only lose.
Ask for more information, and you get toasted by the OP for asking
questions instead of serving instant gratification.
Make a wrong assumption, and you get toasted by the OP for giving an
incorrect answer.
Make a correct assumption, and some smartypant other poster informs you
that it might have been wrong.....
My post was, of course, not intended as criticism, but as additional
information and a warning for a common pitfall, both to the OP and to
any others that might be reading this thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> My post was, of course, not intended as criticism,
Don't worry, it wasn't taken as such...sql

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