MySQL: get each latest entry from joined table

Question

I'm trying to build a Database (SQLFiddle below) for Items (let them be any kind of machine, in this example cars and bikes) which probably have to be checked at a given interval against one or more CheckTypes.

An Item has many ItemsCheckTypes:

• Item Car1 has to be checked every 2 years at a garage and every year by sight
• Item Car2 has only to be checked at the garage
• Item Bike2 does not have to be checked at all.

A Check belongs to a CheckType and has many ItemsChecks:

• A Check on 2018-01-01 with CheckType "Garage inspection", Car1 & Car2 were checked (ItemsChecks does the assignment)

What i want now is a query to find out the last time, an Item has been checked for each of it's ItemsCheckTypes. Expected is a result like this:

Items.id | CheckTypes.id | LAST CHECK.date | LAST CHECK.id
1        | 1             | 2018-01-01      | 4
1        | 2             | 2017-08-01      | 3
2        | 1             | 2016-01-01      | 1

I tried many JOINS, subqueries and other approaches, tried to figure out which table is my starting point (I'm thinking of ItemsChecks or Items) - but right now I just don't know if it is possible at all or should I do one of the following to (hopefully) simplify it:

• create some kind of cache / helper-table which stores the last check's id, date and type_id - being overwritten on new check assignment if date is greater than the current last date (might be a new possible source off errors)
• come up with a better database design
• do a new query for each item (which i think might be easier but wont give me the option to find all items which have to be checked soon)

On application-level I'm using CakePHP 3.x, but i doubt a Cake-solution would be easier than an SQL-solution. The model-associations seem fine, everything else is working as expected.

Finally the SQL-Fiddle: http://sqlfiddle.com/#!9/b3d1f8/17

Any help or suggestions is appreciated, I don't know which way to go...

Answer 1

Group the checks by item_id and check_type_id and get the latest date:

select
    ic.item_id, 
    c.check_type_id, 
    max(c.date) latest
from items_checks ic
inner join checks c on c.id = ic.check_id
group by 
    ic.item_id, 
    c.check_type_id
;

check_id is still missing. So we take the previous select and join it with the table checks to get it.

select
    c2.item_id,
    c2.check_type_id,
    c2.latest last_check,
    c1.id check_id
from checks c1
inner join (
    select ic.item_id, c.check_type_id, max(c.date) latest
    from items_checks ic
    inner join checks c on c.id = ic.check_id
    group by ic.item_id, c.check_type_id
) c2 on c2.check_type_id = c1.check_type_id and c2.latest = c1.date
order by
    c2.item_id,
    c2.check_type_id
;