Posted by Erland Sommarskog on 12/04/07 09:25

Jeff Kish (kishjjrjj@charter.net) writes:
> Can anyone tell me how to do this in sql server?
> I am currently doing this query in oracle:
>
> select table1.col1,table1.col2,table2.col3,table4.col4
> where table1.col1 = table2.col3 and
> table2.col3 = table4.col5 and
> (table1.col1,table1.col2) not in
> select table2.col4,table2.col5 from table2
>
>
> it is the where two column values from any row are not found in any
> row in table2 part that I can't figure out.

Row constructors is part of the ANSI Stanard, but not implemented in
SQL Server. So it's only to chalk one up for Oracle there.

Fortunately, in this situation you could just as well use NOT EXISTS:

select ...
from table1 t1
where not exists (select *
from table2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]