Sorry if what I'm trying to ask is confusing, I'm oversimplifying a very complex query to go to the point of the problem.
Let's say I've got TableA with the following columns: item_id; price; date.
I also have a TableB with the same three colums, and of both tables only the ID match.
Example:
Table A:
+---------+-------+------------+
| item_id | price | date |
+---------+-------+------------+
| 1 | 60 | 2020/08/04 |
+---------+-------+------------+
| 2 | 100 | 2020/07/23 |
+---------+-------+------------+
Table B:
+---------+-------+------------+
| item_id | price | date |
+---------+-------+------------+
| 1 | 80 | 2020/08/05 |
+---------+-------+------------+
| 2 | 70 | 2020/07/24 |
+---------+-------+------------+
| 2 | 90 | 2020/07/20 |
+---------+-------+------------+
| 2 | 60 | 2020/06/20 |
+---------+-------+------------+
I want to join these 2 tables using MySql, but with the condition that the date of TableB has to be the closest to the date of TableA, but prior.
Expected result:
+---------+---------+---------+
| item_id | price A | price B |
+---------+---------+---------+
| 1 | 60 | NULL | (As B date is superior to A)
+---------+---------+---------+
| 2 | 100 | 90 | (I ignore the superior(s), and of the other 2 I take max date)
+---------+---------+---------+
I tried to do a LEFT JOIN but it adds multiple rows on the output, and I'm not really sure how to correctly write the conditions. I'm new to writing complex querys like this, the only thing I knew 2 days ago were really basic, and so when I tried to google ways to do this I only ended more confused.
Thanks for reading!
I think the simplest approach here is a correlated subquery that recovers rows from the second table that match on id and have an earlier date, and then brings the price of the "top" row:
select
a.item_id,
a.price price_a
(
select b.price
from tableb b
where b.id = a.id and b.date < a.date
order by b.date desc
limit 1
) price_b
from tablea a
For performance, consider an index on tableb(id, date, price).