sql - Finding the differences between tables with same schema, with changes and original values in same output -

i have 2 tables, each same schema.

the first 1 (or olddatatable here on) looks this:

| plantid | outletid  | businesstypeid | tradechannelid | |---------|-----------|----------------|----------------| | i000    | 500113730 |              1 |              8 | | i000    | 500113772 |              1 |             12 | | i000    | 500113819 |              1 |             40 | | i000    | 500113821 |              1 |              8 | | i000    | 500113848 |              1 |              7 | 

the second 1 (or newdatatable here on) looks this:

| plantid | outletid  | businesstypeid | tradechannelid | |---------|-----------|----------------|----------------| | i000    | 500113730 |              2 |              5 | | i000    | 500113772 |              1 |             12 | | i000    | 500113819 |              1 |             40 | | i000    | 500113821 |              1 |              8 | | i000    | 500113848 |              1 |              7 | 

you can see, there differences. given outletid (500113730), businesstypeid , tradechannelid changed in newdatatable opposed olddatatable.

i can't figure out query accomplishes looking for. need have query produces output shows outletid changed , changed, along original value. given 2 examples above, result should this:

| plantid | change_plantid | outletid  | change_outletid | businesstypeid | change_businesstypeid | tradechannelid | change_tradechannelid | |---------|----------------|-----------|-----------------|----------------|-----------------------|----------------|-----------------------| | i000    |                | 500113730 |                 |              1 |                     2 |              8 |                     5 | 

couple things note:

the application not know changed, if changed. output shows outletids changed.

it should easy enough join 2 tables together, using constraint checks different:

select     old.plantid     ,old.outletid     ,old.businesstypeid     ,new.businesstypeid change_businesstypeid     ,old.tradechannelid     ,new.tradechannelid change_tradechannelid     olddatatable old full outer join     newdatatable new     on     old.plantid = new.plantid     ,     old.outletid = new.outletid     (         old.businesstypeid <> new.businesstypeid         or         old.tradechannelid <> new.tradechannelid     ) 

what won't hide old/new pairs of 1 column didn't change while column in same row did, add checks in select list (e.g. select case when old.column <> new.column old.column end).

update - here's statement might if want handle case changed per column included in results:

select     old.plantid     ,old.outletid     ,(case when old.businesstypeid <> new.businesstypeid old.businesstypeid end) businesstypeid     ,(case when old.businesstypeid <> new.businesstypeid new.businesstypeid end) change_businesstypeid     ,(case when old.tradechannelid <> new.tradechannelid old.tradechannelid end) tradechannelid     ,(case when old.tradechannelid <> new.tradechannelid new.tradechannelid end) change_tradechannelid     olddatatable old full outer join     newdatatable new     on     old.plantid = new.plantid     ,     old.outletid = new.outletid     (         old.businesstypeid <> new.businesstypeid         or         old.tradechannelid <> new.tradechannelid     )