Nsryjdtyk

status_tb

+----+----------+-------------+----------+

| id | status   | description | state_id |

+----+----------+-------------+----------+

|  1 |     new  | north       | 1        |

|  2 |   assign | south       | 2        |

|  3 |Postponed | east        | 2        |

|  4 |    Fixed | west        | 3        |

|  8 | Verified | north-east  | 1        |

|  9 |   Closed | south-west  | 2        |

| 35 |     Test | South-test  | 4        |

+----+----------+-------------+----------+

status_backup_tb

+----------+----+----------+-------------+----------+

|backup_id | id | status   | description | state_id |

+----------+----+----------+-------------+----------+

|  1       |  1 |new       | north       | 1        |

|  2       |  2 |assign    | south       | 2        |

|  3       |  3 |Postponed | east        | 2        |

|  4       |  4 | Fixed    | west        | 3        |

|  5       |  8 | Verified | north-east  | 1        |

|  6       |  9 | Closed   | south-west  | 2        |

|  7       |  35| Rejected | Testing     | 4        |

+----------+----+----------+-------------+----------+

Wanted result: Column_changed only, old_value and new_value

 |new_id | id |Column_changed| Old_value   |New_value |

 +-------+----+--------------+-------------+----------+

 |1      | 35 | status       | Test        | Rejected |

 |2      | 35 |description   | South-test  | Testing  |  

 +-------+----+--------------+-------------+----------+

If state_id and status was the one who changed, I wanted to get id, status and state_id column and their old_values and new_values instead.

Already try using that but didnt work

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...

FROM

(

 SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

 FROM A

 UNION ALL

 SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

 FROM B

 ) tmp

GROUP BY ID, COL1, COL2, COL3 ...

HAVING COUNT(*) = 1

ORDER BY ID

One way would be to get all changes for respective fields in individual Select queries. Eventually Union the results of these multiple queries.

We JOIN between the two tables using id and the condition that the corresponding column values are not matching.

(SELECT 

   s.id, 

   'status' AS Column_changed, 

   s.status AS Old_value, 

   b.status AS New_value 

 FROM status_tb AS s

 JOIN status_backup_tb AS b 

   ON b.id = s.id AND 

      b.status <> s.status)



UNION ALL 



(SELECT 

   s.id, 

   'description' AS Column_changed, 

   s.description AS Old_value, 

   b.description AS New_value 

 FROM status_tb AS s

 JOIN status_backup_tb AS b 

   ON b.id = s.id AND 

      b.description <> s.description)



UNION ALL



(SELECT 

   s.id, 

   'state_id' AS Column_changed, 

   s.state_id AS Old_value, 

   b.state_id AS New_value 

 FROM status_tb AS s

 JOIN status_backup_tb AS b 

   ON b.id = s.id AND 

      b.state_id <> s.state_id)



ORDER BY id

Try below :

((select a.id, 'status' as Column_changed, a.status, b.status

From status_tb as a inner join status_backup_tb as b on a.id = b.id

Where a.status <> b.status)

UNION

(select a.id, 'description' as Column_changed, a.description, b.description

From status_tb as a inner join status_backup_tb as b on a.id = b.id

Where a.status <> b.status))

UNION

((select a.id, 'status' as Column_changed, a.status, b.status

From status_tb as a inner join status_backup_tb as b on a.id = b.id

Where a.status_id <> b.status_id)

UNION

(select a.id, 'description' as Column_changed, a.description, b.description

From status_tb as a inner join status_backup_tb as b on a.id = b.id

Where a.status_id <> b.status_id))