• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1922
  • Last Modified:

'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
0
cdfllc
Asked:
cdfllc
  • 3
  • 2
1 Solution
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now