MySQL Compare Tables and generate delta

Question

I have two exact tables with different data. I need to be able to generate a Mysql View with the following:

1. All new rows that don't appear in previous_table but appears in current_table.
2. All rows in previous_table that has updated column values in current_table.

The tables don't have any primary keys. I cannot change the schema because it is out of my control.

Any help would be deeply appreciated.

Answer 1

1)

SELECT t1.*
FROM current_table t1
LEFT JOIN previous_table t2 ON t1.field1 = t2.field1
                           AND t1.field2 = t2.field2
                     --    another fields in tables structures
                           AND t1.fieldN = t2.fieldN
WHERE t2.fieldX IS NULL /* fieldX - any field defined as NOT NULL */

2)

SELECT t2.*, 
       t1.fieldZ -- new value
FROM current_table t1
INNER JOIN previous_table t2 ON t1.field1 = t2.field1
                            AND t1.field2 = t2.field2
                      --    another static fields in tables structures
                            AND t1.fieldN = t2.fieldN
                      --    altered field
                            AND t1.fieldZ != t2.fieldZ
                      /* if more than one field may be changed, then
                            AND (     t1.fieldZa != t2.fieldZa
                                   OR t1.fieldZb != t2.fieldZb
                               --  OR ...
                                )
                      */ 

Answer 2

Query 1:

SELECT *
    FROM current_table AS c
    LEFT JOIN previous_table AS p USING(some-combo-of-columns)
    WHERE (any-NOT-NULL-column-in-p) IS NULL;

It is quite naughty not to have a PRIMARY KEY on every table. Do you have a UNIQUE key? Such would work just as well (for this task).

Query 2:

SELECT c.*
    FROM current_table AS c
    LEFT JOIN previous_table AS p USING(some-combo-of-columns)
    WHERE (OR-of-!=-comparing-any-columns-that-might-be-different)

Please have more specifics in your question; then I can provide more specifics.