'Reverse' Inner Join?

I am sure there is a very easy way to do this, and I am just too tired to even know how to search for it.
I want to get back the rows in one table that are NOT in another table...
Something like this should have worked (I would have thought) but brought back no results...


select * from UploadContactList$ c where c.firstname + ' ' + c.lastname not in (SELECT p.party_firstname + ' ' + p.party_lastname FROM party p)


thanks!
ccfllc
LVL 1
cdfllcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
select * from UploadContactList$ c
left join party p
 on c.firstname = p.firstname
and c.lastname = p.lastname
where c.firstname is null
and c.lastname is null
0
cdfllcAuthor Commented:
angelIII, it's still not returning any rows.

In my upload table, I have a row with
firstname= David
lastname= Price

No such record is in my party table - there are Scott Price, Karen Price, Joan Price, etc
but no David

and that is the row that I want to be returned...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the where clause:

select *
from UploadContactList$ c
left join party p
 on c.firstname = p.firstname
and c.lastname = p.lastname

I understood you wanted only those that are missing...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cdfllcAuthor Commented:
Sorry angelIII, I don't know if something weird is going on here or not...
I know that you are a guru at this stuff, and your solution probably should be working
but here are my results:

UploadContactList$ - table contains 3 rows
firstname     lastname
Laura          Jones
David          Price
Elton           John


party - table contains 17000+ rows (some examples below)
Laura          Jones
Elton           John



when I run this query:

select *
from UploadContactList$ c
left join party p
 on c.firstname = p.firstname
and c.lastname = p.lastname


I get this:
Laura          Jones
Laura          Jones
Elton           John
David          Price

I assume that Laura Jones is in the party table twice...

But what I really want to get back is just one row:
David          Price

Because he is not in the party table... That is what I am trying to determine - what rows am I uploading taht are not already in our party table...

Can you tell me if I *should* only be getting back that one row, and some data is just messed up or something?

thanks for helping! I do appreciate it!
ccfllc
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see now that I made an error in my first suggestion, the WHERE clause was slighly wrong:

select *
from UploadContactList$ c
left join party p
 on c.firstname = p.firstname
and c.lastname = p.lastname
where p.firstname is null
and p.lastname is null

This should work better:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
morisceCommented:
left join invert the desired result.
i think that the first query is a good one but perhaps there is some string mistakes (blanks, ...)

Try :
select * from UploadContactList$ c where not exists (SELECT 1 FROM party p where c.firstname = p.party_firstname and c.lastname = p.party_lastname )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.