x

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.
more ▼

asked Jul 25 '11 at 05:11 AM in Default

mikelanders gravatar image

mikelanders
329 4 5 8

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?
Jul 25 '11 at 06:26 AM WilliamD
To see if the values exist in table2.
Jul 25 '11 at 06:44 AM mikelanders
Ok, but is that to see if the values of ColumnA are in ColumnA, or in any column of that table?
Jul 25 '11 at 07:04 AM WilliamD
if the values columnA and columnB in table1 are in columnA and columnB in table2.
Jul 25 '11 at 07:05 AM mikelanders
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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
more ▼

answered Jul 25 '11 at 05:41 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

Worked like a champ. Thanks @Fatherjack.
Jul 25 '11 at 07:14 AM mikelanders
No problem, the "I'm sure I used to know how to do this" feeling happens to us all from time to time. :)
Jul 25 '11 at 11:29 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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
)
more ▼

answered Jul 26 '11 at 04:02 AM

Martin 1 gravatar image

Martin 1
203 8 9 11

(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Jul 28 '11 at 05:07 AM

SHAHID 1 gravatar image

SHAHID 1
12 1 1

(comments are locked)
10|1200 characters needed characters left
SELECT * FROM T1 WHERE LTRIM(RTRIM(T1.C1))+LTRIM(RTRIM(T1.C2)) NOT IN (SELECT LTRIM(RTRIM(C1))+LTRIM(RTRIM(C2)) FROM T2)
more ▼

answered Jul 28 '11 at 05:08 AM

SHAHID 1 gravatar image

SHAHID 1
12 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977

asked: Jul 25 '11 at 05:11 AM

Seen: 1581 times

Last Updated: Jul 25 '11 at 05:11 AM