vendredi 9 octobre 2015

Find difference between tables where values differ

When I search for how to compare two tables in SQLite, and see what's differ, I mostly find answers like this:

SELECT B.id FROM B LEFT JOIN A ON B.id = A.id WHERE A.id IS NULL

and yes, it's correct if you want do find all the elements (or values for keys named 'id' in this case) in table B that is not in table A, i.e. all the new elements in B if B is a later version of A.

But I want more than that. I want to find all the id:s in B where the value for a certain key (or keys) deviate from the corresponding value in A. For example, if I have two tables, A and B, with id:s and positions (x_value, y_value), I want to get the result id=3 in this case, because it is the element in B that has a value that differ. What would be the easiest way to do that?

Table A                      Table B

id | x_value | y_value       id | x_value | y_value
-----------------------      -----------------------
1  | 29.9563 | 12.6764       1  | 29.9563 | 12.6764
2  | 45.5843 | 7.6733        2  | 45.5843 | 7.6733 
3  | 28.2313 | 15.6579       3  | 39.2003 | 15.6579

Result:

id
--
3

Aucun commentaire:

Enregistrer un commentaire