Is there a way to track changes on two tables using SQL or and EXCEL?-CodePudding

[Old table][1]I have a problem I was hoping someone here could help me with. I have 2 tables (old and new) with very similar data (equal columns and number of rows), but with a few differences in some values. I [New table][2]would like to track the differences in values (data) between these 2 tables. My thought is to use LEFT JOIN or INNER JOIN both of which are not giving the result I would like. It should be noted that the ID which would normally be the primary key does not contain unique values (I added a Serial_Number column to solve this). I really just need to see the values that have changed. From the images, you will notice that there the differences in the Staff_ID and Amount on 5 and 6. There are instances where the Staff_ID is the same but the Amount has changed.

Approach 1:

FROM Database_2
LEFT JOIN Database_1 
ON  Database_2.Staff_ID = Database_1.Staff_ID```

Approach 2:
```SELECT * 
FROM Database_2
INNER JOIN Database_1 
ON  Database_2.Staff_ID = Database_1.Staff_ID```

Approach 3: (I added a Serial_Number column. This is not even working)
```SELECT Serial_Number, Staff_ID, PT, Price  FROM Database_1 
EXCEPT
SELECT Serial_Number, Staff_ID, PT, Price from Database_2;```


  [1]: https://i.stack.imgur.com/2kXVg.png
  [2]: https://i.stack.imgur.com/UqI5V.png

CodePudding user response：

To see the rows in new that aren't in old:

SELECT * FROM new a
WHERE NOT EXISTS (
    SELECT 1 FROM old b
    WHERE a.Serial_Number = b.Serial_Number 
    AND a.Staff_ID = b.Staff_ID
    AND a.PT = b.PT
    AND a.Price = b.Price
)

And similar to see the rows in old that aren't in new.

CodePudding user response：

I suggest you use an Excel book that uses REALLY absolute references to cells, so you can paste "new" in one sheet, "old" in another sheet, and in a third sheet you can easily see the differences. The reason to have REALLY absolute references is to be able to delete/add rows and/or columns in the sheets "new" and "old", while Excel still compares cell to cell by their absolute position. An added advantage of doing the comparison in Excel is that you can set a threshold on when two corresponding values from "new" and "old" are considered the same, in order to ignore "false alarms" of small differences caused by binary arithmetic.

You may download such an Excel book from LightningGuide.net, called record-list comparator.