asked
Returning values based on two columns not existing in another table.
I am having a mental block this morning. Trying to write a query to only return values where they do not exist in another table but it needs to be where columnA and columnB in table1 doesn't exist in table2. I know for joins I can select whatever from table1 a join table2 b on a.columnA = b.columnA and a.columnB = b.columnB but how to I select whatever from table1 where columnA and columnB not in table2? Again, I fill like a dope for not being able to grasp this so early on a Monday morning. If it was just a single column no worries, its the two fields that is kicking my tale.
if the values columnA and columnB in table1 are in columnA and columnB in table2.
Does this do what you need? USE [adventureworks] go SELECT COUNT(*) FROM [Person].[Contact] AS c SELECT COUNT(*) FROM [HumanResources].[Employee] AS e -- all contacts that are employees SELECT [c].[FirstName] , [c].[LastName] FROM [Person].[Contact] AS c JOIN [HumanResources].[Employee] AS e ON [c].[ContactID] = [e].[ContactID] -- all contacts that are not employees SELECT [c].[FirstName] , [c].[LastName] FROM [Person].[Contact] AS c left JOIN [HumanResources].[Employee] AS e ON [c].[ContactID] = [e].[ContactID] WHERE [e].[ContactID] IS NULL
No problem, the "I'm sure I used to know how to do this" feeling happens to us all from time to time. :)
This is known as an anti semi join. If you only care about the A/B values you can use SELECT columnA, columnB FROM table1 EXCEPT SELECT columnA, columnB FROM table2 If you want additional columns from table1 you can use SELECT columnA, columnB, columnC, ... FROM table1 t1 WHERE NOT EXISTS ( SELECT * FROM table2 t2 WHERE t1.columnA = t2.columnA AND t1.columnB = t2.columnB )
select * from table T1 where ltrim(rtrim(T1.C1))+ltrim(rtrim(T1.C2) not in (selet ltrim(rtrim(C1))+ltrim(rtrim(c2)) from T2) GO
SELECT * FROM T1 WHERE LTRIM(RTRIM(T1.C1))+LTRIM(RTRIM(T1.C2)) NOT IN (SELECT LTRIM(RTRIM(C1))+LTRIM(RTRIM(C2)) FROM T2)
