How to pull the last order entry from the log?

Question

MySQL

Hi friends!
There is a table with orders orders
id, fio, phone, date_create
And there is a table with a history of order statuses orders_log
id, order_id, action, date
where action is the statuses ala CREATED, BUILDED, SHIPPED, HANDED in chronological order (by date)
i.e. in order to understand what the current status of a counter order is, you need to extract a record from the log with this order and the maximum date.
Question: Show how you can pull out all orders + actual current statuses for them through join?

id, fio, phone, date_create, status
1, Иванов, +7929, 2018-02-02, SHIPPED
2, Петров, +7982, 2018-02-08, BUILDED

Thank you!

Answer 1

SELECT
 _ord.*,
(SELECT _ol.`action` FROM `orders_log` _ol WHERE _ol.order_id = _ord.id ORDER BY _ol.`date` DESC LIMIT 1) AS status
FROM `orders` _ord

add a composite index (order_id, date DESC) to the orders_log table