question

mikelanders avatar image
mikelanders 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.
t-sql
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
I'm not too sure what you mean. Is this a table meta-data select to see if the columns exist, or is it to check if the values in table1 ColA and ColB don't exist in table2 ColA and ColB respectively, or something completely different?
0 Likes 0 ·
mikelanders avatar image mikelanders commented ·
To see if the values exist in table2.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Ok, but is that to see if the values of ColumnA are in ColumnA, or in any column of that table?
0 Likes 0 ·
mikelanders avatar image mikelanders commented ·
if the values columnA and columnB in table1 are in columnA and columnB in table2.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mikelanders avatar image mikelanders commented ·
Worked like a champ. Thanks @Fatherjack.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
No problem, the "I'm sure I used to know how to do this" feeling happens to us all from time to time. :)
0 Likes 0 ·
Martin 1 avatar image
Martin 1 answered
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 )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SHAHID 1 avatar image
SHAHID 1 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SHAHID 1 avatar image
SHAHID 1 answered
SELECT * FROM T1 WHERE LTRIM(RTRIM(T1.C1))+LTRIM(RTRIM(T1.C2)) NOT IN (SELECT LTRIM(RTRIM(C1))+LTRIM(RTRIM(C2)) FROM T2)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.