Looking for SQL query to display data from 2 tables that is not commone between the tables.

Question

I have two tables that have the exact same structure.

Table MasterList Acct_id(9) Name (25) Address (35) City(15) State(2) ZipCode(5)

and

Table NewMasterList Acct_id(9) Name (25) Address (35) City(15) State(2) ZipCode(5)

I need a query that will display the Acct_ID and Name for all records from each table that are NOT in BOTH tables.

Answer 1

Assuming acct_ID is the primary key:

SELECT COALESCE(ml.acct_ID, nml.acct_ID) AS AccountID
     , COALESCE(ml.name, nml.name) AS Name 
FROM MasterList ml
    FULL OUTER JOIN NewMasterList nml ON nml.Acct_ID=ml.Acct_ID
WHERE nml.acct_ID IS NULL OR ml.acct_ID IS NULL

Answer 2

Hi

I presume the following query should help-

select Acct_id,Name 
from MasterList 
where Acct_id not in (select distinct Acct_id from NewMasterList) 

union 

select Acct_id,Name 
from NewMasterList 
where Acct_id not in (select distinct Acct_id from MasterList)

cheers