How to match sales and purchase data using the joins in MySQL?

Question

I need to join price_bought from table products_purchased to a sales table, and then calculate the proper profit margin in the sales table.

SALES

dt	product	qty	total
2023-05-01	X	1	150
2023-05-01	X	2	300
2023-05-01	Y	1	100
2023-05-07	X	1	160
2023-05-07	Y	1	110
2023-05-07	Y	2	220

PRODUCTS_PURCHASED

dt	product	price_bought
2023-05-01	X	100
2023-05-01	Y	60
2023-05-07	X	110
2023-05-07	Y	66

What I expect:

SALES TABLE (output)

dt	product	qty	total	price_bought
2023-05-01	X	1	150	100
2023-05-01	X	2	300	100
2023-05-01	Y	1	100	60
2023-05-07	X	1	160	110
2023-05-07	Y	1	110	60
2023-05-07	Y	2	220	66

Answer 1

If there is always a price in products_purchased for all dates when a product is sold, we can just join:

select s.*, p.price_bought
from sales s
inner join products_purchased p using(dt, product)

Note that the join does not consider the quantity, as your data seems to show.

If, on the other hand, there may be sales dates without a corresponding product price, then:

• you could just turn the inner join to a left join to avoid filtering out such rows (and get a null price instead)

• or you might want to retrieve the latest price before that date ; one option uses a correlated subquery:

select s.*, 
    (
        select p.price_bought
        from products_purchased p
        where p.dt <= s.dt and p.product = s.product
        order by d.dt desc limit 1
    ) price_bought
from sales s