Multiple Table Join to display records where 2 columns don't exist in a table

Question

Multiple Table Join to display records where 2 columns don't exist in a table

wdodds

Newbie

Points: 2
More actions
April 20, 2012 at 8:55 am

#254522

I have 2 tables:
TableA
TableB
TableA contains 2 columns: EmployeeID and Code (plus a handful of other elements)
TableB contains 2 columns: EmployeeID and Code (plus a handful of different elements that are not in Table A)
I'm trying to show all records from TableA where the EmployeeID AND the Code for that Employee do not exist in TableB.
At first I was thinking an Outer Join but I cannot figure out the proper syntax to use since I basically want all records from A where the record Employee and Code don't exist in B.
Any help is greatly appreciated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

wdodds (4/20/2012)
I have 2 tables:
TableA
TableB
TableA contains 2 columns: EmployeeID and Code (plus a handful of other elements)
TableB contains 2 columns: EmployeeID and Code (plus a handful of different elements that are not in Table A)
I'm trying to show all records from TableA where the EmployeeID AND the Code for that Employee do not exist in TableB.
At first I was thinking an Outer Join but I cannot figure out the proper syntax to use since I basically want all records from A where the record Employee and Code don't exist in B.
Any help is greatly appreciated.

select

a.EmployeeID,

a.Code

from

dbo.TableA a

left outer join dbo.TableB b

on (a.EmployeeID = b.EmployeeID

and a.Code = b.Code)

where

b.EmployeeID is null;

Give the above a shot.

CapnHector SSCoach Points: 16143 More actions · Answer 2

something like this maby:

SELECT *

FROM TableA a

LEFT JOIN TableB b

ON a.empid = b.empid

AND a.code = b.code

WHERE b.empid IS NULL

with out DDL and Sample data its hard to give tested code. if that does not work can you check out the link in my signature for how we like to see them.

EDIT: lynn you beat me to it.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

wdodds Newbie Points: 2 More actions · Answer 3

Worked like a champ. I knew it was something simple.

Thanks!