Comparing multiple fields between (2) Tables

[awise]

I need help with a script that will compare;
TABLE A, fields LASTNAME, ADDRESS1,CITY,STATE,ZIP with
TABLE B, fields LASTNAME, ADDRESS1,CITY,STATE,ZIP AND

where a record from TABLE A does NOT exist in TABLE B,
returns those records as the results.

We purchased outside customer list and want to compare it
with our existing customer database to identify potential customers in the outside list that are not already existing customers. My thought is that if the LASTNAME+ADDRESS1+CITY+STATE+ZIP concatinations are identical, then that record is a duplicate that already exists in our database.

Appreciate your assistance and replys.

Thank you,

zaw

 

[AlexCuse]

Ah, this is always a fun task

I would do it like this (to get Non-duplicate rows), but you might need to add some trims and such depending on your data format

select listTable.*
from listTable a
left join
CustomerTable b
on a.LASTNAME = b.LASTNAME
and a.ADDRESS1 = b.ADDRESS1
and a.CITY = b.CITY
and a.STATE = b.STATE
and a.ZIP = b.ZIP
where b.LASTNAME is null

The important part is where b.LASTNAME is null, as this is telling the query engine to take only the rows with no matching record in your customer table.

I would also recommend that unless you use any type of Address Standardization software (and it has been run on BOTH tables), you remove CITY from your match key as this can vary a lot (I get junk mail with my city listed as three different cities, ZIP and State is all that really matters)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

I would also consider adding first_name and middle_name to that. If you have customers who are at the same address but with different names, you would match on the first query. For instance we deal with physician's addresses here and many physicians have their sons and daughters in the same practice with them as well as spouses. So I could have four separate records for John Smith, John Smith, Jr., Sally Smith and Sara Smith all at the same address. They would be four separate people though.

Questions about posting. See faq183-874