jeudi 16 juin 2016

MYSQL - JOIN to the last record on right table PREVIOUS to record from left table

I've seen many questions similar to this one, but not quite like this. What i need to do is to LEFT JOIN records, to the immediately previous record on the right table. for example:

LEFT TABLE 'PROD':
idMachine |production | prod.date
1         |10         | 2016-05-02
2         |11         | 2016-05-15
3         |14         | 2016-06-01
3         |13         | 2016-06-15

RIGHT TABLE 'CONFIG':
idMachine | sett   | config.date
1         |  30    | 2016-04-01
2         |  25    | 2016-04-01
3         |  20    | 2016-04-01
1         |  40    | 2016-05-01
3         |  45    | 2016-06-10
1         |  50    | 2016-06-15

and the result should be:
idMachine |production | sett   | prod.date
1         |10         | 40     | 2016-05-02
2         |11         | 25     | 2016-05-15
3         |14         | 20     | 2016-06-01
3         |13         | 45     | 2016-06-15

the system works like this, i record all production runs on machines, and i record every change on the config of each machine, what i need to know, is what settings it had for that production run. If you notice, in the result, for machines 1 and 3, the join is not done to the LATEST record of the config, but to the LAST record before the production happened, in that way we could know what we produced with what settings.

I've tried joins with JOIN CONFIGS ON prod.date > config.date but it joins to EVERY config previous, not only the last. and if I use LIMIT it doesnt give me all the records i need. I've tried some JOINS to subqueries using MAX, but the subquery returns only the latest config, and not the previous configs, so "old" runs dont have configs...

Any help is very appreciated!!!

Aucun commentaire:

Enregistrer un commentaire