Not In SQL, with multiple columns

by Ravi Tuvar 5:06 AM 0 comments

The following pseudoquery is ANSI SQL, but will not work in
MS SQL because I could not use multiple columns in a NOT IN subquery. 
How can I get this result set using Transact-SQL? I keep getting
lost trying to figure it out.


SELECT Field1, Field2, Field3, Field4 
FROM Table1
WHERE 
Field5 = 'Param1'
AND Field1, Field2 
NOT IN
(SELECT Field1, Field2 
FROM Table2 
WHERE Field6 = 'Param2')

So here is the way how can we do this in SQL..

Solution: 

select Field1, Field2, Field3, Field4from Table1 T1
where not exists (
    SELECT Field1, Field2
    FROM Table2 T2
    where T2.Field1 = T1. Field1 and T2 .Field2 = T1 .Field2
)