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