-2

I want to query from tableUSA name_column , address_column, postalcode_column

And

From tableCANADA name_column , address_column, postalcode_column

And basically compare both tables to see if there is data missing in TableUSA

I want to return the difference (whatever that's missing in TableUSA). Please keep in mind that some addresses will use Street, Boulevard, Canyon. While others, will use ST, BLVD, CYN etc. Lastly, there are companies that use Corporations/Corp, LLC/Limited Liability Company, LP/Limited Partnership etc. How can I solve this? I want to avoid manual matching as much as I can.

Thank you

Ishy
  • 9
  • 4
  • 2
    You need to provide some sample data for both tables, what you have tried so far and the issue you are facing!! – techspider Aug 17 '16 at 16:36
  • 2
    Many gold and silver medals will be missing from team Canada. They were won by the USA and China :-) – Tim Biegeleisen Aug 17 '16 at 16:36
  • Is there a PK in your tables? – Hamlet Hakobyan Aug 17 '16 at 16:37
  • Possible duplicate of [SQL - find records from one table which don't exist in another](http://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) – Matt Aug 17 '16 at 16:39
  • @ techspider: Good point. Sorry about that. @ Hamlet Hakobyan: No PK. Just name, Address, postalcode, city and state. @ Matt: This is awesome. But, It doesn't solve my Street vs St, BLVD vs Boulevard, Canyon vs CYN etc. Any suggestions? – Ishy Aug 18 '16 at 11:00

1 Answers1

1

You can use EXCEPT:

SELECT ColumnA, ColumnB, ColumnC
FROM tableCanada
EXCEPT 
SELECT ColumnA, ColumnB, ColumnC
FROM tableUSA;
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • I have two tables (Insurance company information -- from two different sources -- looking at only one state -- CA). First table is: DNB_CA Columns: NAME, ADDRESS1, CITY, STATE, POSTALCODE Second table: CA_State Columns: NAME, ADDRESS1, CITY, STATE, POSTALCODE The problem I'm facing is that some of the addresses have BLVD, ST CYN etc. While others include Street, Boulevard and Canyon. They are the same. Lastly, there are companies that use Corporations/Corp, LLC/Limited Liability Company, LP/Limited Partnership etc. How can I solve this? I want to avoid manual matching as much as I can. – Ishy Aug 18 '16 at 11:31