Show only differences between two rows in mysql -

having (mysql) audit table containing rows similar, possible view columns have different values?

for example, table containing 4 columns column key primary key, , column id identifier match rows:

  key   id   col1  col2 1     123     b     c 2     123         c 3     456     b     c 4     789     b     5     789     b     b 6     987         c  

in example above need query return row 1, 2, 4, , 5 have matching id, , differing values in col1 , col2, ie b,a , b,a.

  key  id   col1   col2 1    123  b 2    123  4    789         5    789         b  

i know might not efficient solution, gives want. here try this:

select a.id, (case a.col1 when b.col1 null else b.col1 end), (case a.col2 when b.col2 null else b.col2 end) tblname full outer join tblname b on a.id=b.id (a.col1=b.col1 , a.col2<>b.col2) or (a.col2<>b.col2 , a.col1=b.col1) 

inner join should give same result