WHERE IN (SELECT) with multiple columns

[Peter]

A continuing annoyance is that I can do stuff like this in SQL Server:

SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)

This is getting more complex if any of A or B can be NULL. Is there an
alternative in SQLServer 2005+ to implement such predicates?

Pete

[sloan]

Select * from dbo.Employee e where exists ( select null from
dbo.SomeOtherTable sot where sot.StateID = e.StateID and sot.EmployeeID =
e.EmployeeID )

You can start there and then experiment.

Throw in a " or e.StateID IS NULL "... or something like that.

You should post some DDL , some INSERTS and then desired results
.................

"Peter" <peteATkapiti.co.nz> wrote in message
news:eJSgqJBZ...@TK2MSFTNGP06.phx.gbl...

[--CELKO--]

You have discovered Standard SQL syntax, but missed the row
constructor notation:

SELECT * FROM X WHERE (a,b) IN (SELECT a, b FROM Y);

SQL Server is behind other products. You wind up using

SELECT *
FROM X
WHERE EXISTS
(SELECT *
FROM Y
WHERE X.a = Y.a
AND X.b = Y.b);

[Peter]

Yes you're right, I (accidentially) did missed the row constructor syntax in
my original post.

Still, this "Standard SQL Syntax" works fine in Oracle and MySQL but NOT SQL
Server 2005. The SELECT in the following SQL will give you a syntax error.

CREATE TABLE A (x int, y int )
CREATE TABLE B (x int, y int )
INSERT INTO A (x,y) VALUES (1,1)
INSERT INTO A (x,y) VALUES (1,3)
INSERT INTO A (x,y) VALUES (2,1)
INSERT INTO A (x,y) VALUES (2,3)
INSERT INTO B (x,y) VALUES (1,1)
INSERT INTO B (x,y) VALUES (1,2)
INSERT INTO B (x,y) VALUES (1,3)
INSERT INTO B (x,y) VALUES (2,1)
INSERT INTO B (x,y) VALUES (2,2)
INSERT INTO B (x,y) VALUES (2,3)
SELECT * FROM B WHERE (x,y) IN (SELECT x,y FROM A)
DROP TABLE B
DROP TABLE A

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:a96e328b-ed78-4650...@o10g2000yqa.googlegroups.com...

[Plamen Ratchev]

Yes, vector expressions in predicates are still not supported in SQL Server. The current method using the EXISTS
predicate can be very verbose especially when NULLs are involved. Hope we see something in the next versions.

--
Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

>> Hope we see something in the next versions. <<

Me, too! And I hope to help with it.