Displaying the correct condition found in WHERE clause

Question

I have two tables that have the same columns. If a change occurs it is being recorded in table2 and then I compare table1 to table2 and see if they are different. If they are different I conclude there was a change and I would like to display that in my resulting table.

For example:

SELECT t1.name, t1.age, t1.profession, column_changed, old_value, new_value
FROM   table1 t1, table2 t2
WHERE  t1.column1<>t2.column1 
    OR t1.column2<>t2.column2 
    OR t1.column3<>t2.column3

Of course this query isn't correct. I would like the column_changed, old_value, new_value display the relevant values.

Any ideas?

Answer 1

Does Age, name, profession form a primary key (or at least a unique key?):

If so, you could do something like:

SELECT 
  t1.name, t1.age, t1.profession, 
  t1.column1 as t1column1, t2.column1 as t2column1, 
  t1.column2 as t1column2, t2.column2 as t2column2, 
FROM   
  table1 t1, table2 t2 
WHERE 
  (t1.name = t2.name and t1.age = t2.age and t1.profession = t2.profession) and
  (
   t1.column1<>t2.column1      
   OR t1.column2<>t2.column2     
   OR t1.column3<>t2.column3) 

Of course, that requires a unique key which is the same across both tables. Also, I clearly changed the results of the query to show all columns instead of just the one that changed. Identifying the one that changed like that in a single T-SQL Query is awkward (but possible) so my recommendation would be to return it like this and depending on your use case have the application/presentation layer handle finding which column changed or just scan it by eye.

If you really want to do it in T-SQL, you could do it with UNIONS, like:

SELECT 
  t1.name, t1.age, t1.profession, 
  'column1' as ColumnChanged,
  t1.column1 as oldValue,
  t2.column1 as newValue
FROM   
  table1 t1, table2 t2 
WHERE 
  (t1.name = t2.name and t1.age = t2.age and t1.profession = t2.profession) and
   t1.column1<>t2.column1  
UNION ALL #better peformance than UNION, which would enforce uniqueness
SELECT 
  t1.name, t1.age, t1.profession, 
  'column2' as ColumnChanged,
  t1.column2 as oldValue,
  t2.column2 as newValue
FROM   
  table1 t1, table2 t2 
WHERE 
  (t1.name = t2.name and t1.age = t2.age and t1.profession = t2.profession) and
   t1.column2<>t2.column2 
.......