T-SQL "Where not in" using two columns

user_1427758 0
Viewed 45924

I want to select all records from a table T1 where the values in columns A and B has no matching tuple for the columns C and D in table T2.

In mysql “Where not in” using two columns I can read how to accomplish that using the form select A,B from T1 where (A,B) not in (SELECT C,D from T2), but that fails in T-SQL for me resulting in "Incorrect syntax near ','.".

So how do I do this?

tsql sql
4 Answers

Here is an example of the answer that worked for me:

SELECT Count(1) 
    FROM LCSource as s
    JOIN FileTransaction as t
    ON s.TrackingNumber = t.TrackingNumber  
    WHERE NOT EXISTS (
        SELECT * FROM LCSourceFileTransaction 
        WHERE [LCSourceID] = s.[LCSourceID] AND [FileTransactionID] = t.[FileTransactionID]
    )

You see both columns exist in LCSourceFileTransaction, but one occurs in LCSource and one occurs in FileTransaction and LCSourceFileTransaction is a mapping table. I want to find all records where the combination of the two columns is not in the mapping table. This works great. Hope this helps someone.

user_17703500
Related