Mysql – Easily show rows that are different among multiple tables

MySQL

Imagine you have several tables with same structure (each table, one year of data):

table states_2017

stateID propertyID  owner         area  timestampGot
1       1           David Smith   15    123456789
2       2           Amanda Green  74    123456799
3       1           David Smith   15    123456999

table states_2018

stateID propertyID  owner          area timestampGot
1       3           Amanda Green   12   123459999
2       1           Peter Black    11   123499999
3       4           Jack Stone     62   123999999
4       2           Amanda Green   70   129999999

Then you have table properties with basic information about properties:

table properties

propertyID  defaultStateID  defaultStateYear
1           1               2017
2           2               2017
3           1               2018
4           3               2018

In the example above, state of propertyID 1 changed from its default state (stateID 1 in states_2017) to the most recent state (stateID 2 in states_2018). Similarly, state of propertyID 2 changed. How to show these changes (using MySQL)?

Desired output:

propertyID  owner         area  timestampGot
1           David Smith   15    123456789
1           Peter Black   11    123499999
2           Amanda Green  74    123456799
2           Amanda Green  70    129999999

Best Answer

The wanted query is not trivial at all but it would be a bit easier to write if there was one table and not one per year. So let's create a VIEW:

CREATE VIEW states AS
    SELECT stateID, propertyID, owner, area, timestampGot, 2010 AS year
    FROM states_2010
    UNION ALL
    ...
    SELECT stateID, propertyID, owner, area, timestampGot, 2017
    FROM states_2017
    UNION ALL
    SELECT stateID, propertyID, owner, area, timestampGot, 2018
    FROM states_2018
    ... ;

Then, I assume that you want to get data from the earliest and the latest date per product. So we can use one of the (many) ways to get a greatest-n-per-group result (applied twice, one for the earliest date and another for the latest):

select 
    p.propertyid,
    s.owner,
    s.area,
    s.timestampgot,
    s.stateid
from properties as p
  left join states as s                    -- earliest timestamp
  on  s.propertyid = p.propertyid          -- for this property
  and (s.year, s.timestampgot) =
      ( select si.year, si.timestampgot
        from states as si
        where si.propertyid = p.propertyid
        order by si.year, si.timestampgot
        limit 1
      )
union all
select 
    p.propertyid,
    s.owner,
    s.area,
    s.timestampgot,
    s.stateid
from properties as p
  left join states as s                    -- latest timestamp
  on  s.propertyid = p.propertyid
  and (s.year, s.timestampgot) =
      ( select si.year, si.timestampgot
        from states as si
        where si.propertyid = p.propertyid
        order by si.year desc, si.timestampgot desc
        limit 1
      ) ;