There is a MySQL table with orders and the timestamp and id of these orders, and there is a table with order processing, but there can be several processing. bundle by order id.
I would like to sample orders for a certain date interval (BETWEEN) and attach to it one processing of this order by the “first” of all for this order, well, in fact, the closest one by date.
Is it possible to do this in one request? In essence, you need to limit the LEFT JOIN to one line, there is a lot of things on this subject, but I can't do something.
Meaning in SQL:

SELECT o.id,o.date,p.date.p.state,p.some_column FROM orders o LEFT JOIN process p ON p.order=o.id AND p.date>=o.date ORDER BY p.date LIMIT 1 WHERE o.date BETWEEN '2018-01-21 08:00' AND '2018-01-21 21:00' ORDER BY o.date

    1 answer 1

     select o.id, o.date, p.date, p.state, p.some_column FROM orders o left join (select p.* from process p join (select p.order, min(p.date) as date from process p where p.date >= '2018-01-21 08:00' group by p.order) x on p.order = x.order and p.date = x.date) p on o.id = p.order WHERE o.date BETWEEN '2018-01-21 08:00' AND '2018-01-21 21:00' ORDER BY o.date
    • from p.order so it should be? - Spider
    • No, it was sealed, instead of from p.order it should be from process p. - KadafiVSC
    • Well, I thought so. Thank. I try. - Spider