I have a datasheet list for some products where I need to calculate the cost to make one product taking into account only the latest purchases made of each item in the datasheet.
For example, to make a cake I will use the items as follows:
tb_sheet
id | product | total_cost
1 | "Cake" | 10.25
tb_sheet_product
id | id_sheet | id_product | quantity
1 | 1 | 7 (weat) | 0.400
2 | 1 | 8 (egg) | 2.000
3 | 1 | 9 (salt) | 0.010
This is the data structure for the purchases tables:
tb_purchase
id | date_purchase | value_products | value_shipping
tb_purchase_product
id | id_purchase | id_product | quantity | value | value_total
The following query is returning the results almost correctly. I can filter and get the list of id and value only for the products that belong to the datasheet. However, if there were 10 purchases of the product, 10 results will appear in the query, and I only need the last record based on the purchase date. Remember that I must take into account the field date_purchase, because purchases are not registered in sequential order.
SELECT a.id_product, a.value, b.date_purchase
FROM tb_purchase_product a
INNER JOIN tb_purchase b ON b.id = a.id_purchase
INNER JOIN tb_sheet_product c ON c.id_product = a.id_product
INNER JOIN tb_sheet d
ON d.id = :id_sheet //received as variable
AND d.id = c.id_sheet
How can I fix the query to return only the desired result? Or is there another way to do it? This is how it's returnin currently:
id_product | value | date_purchase
3 | 14.89 | 2019-10-12
5 | 07.90 | 2019-10-09
5 | 08.90 | 2019-10-01
3 | 13.90 | 2019-10-09
5 | 08.90 | 2019-09-28
3 | 14.80 | 2019-09-27
9 | 09.90 | 2019-10-10
9 | 09.90 | 2019-09-07
5 | 07.99 | 2019-09-23
3 | 14.80 | 2019-09-25
9 | 10.90 | 2019-09-30
And this is what I need:
id_product | value | date_purchase
3 | 14.89 | 2019-10-12
5 | 07.90 | 2019-10-09
9 | 10.90 | 2019-10-10
I have also tried using MAX(date_purchase) but what happens is that it takes the maximum date for that product ID but the value is not correlated to that purchase, like this:
SELECT a.id_product, a.value, MAX(b.date_purchase) as 'date_purchase'
FROM tb_purchase_product a
INNER JOIN tb_purchase b ON b.id = a.id_purchase
INNER JOIN tb_sheet_product c ON c.id_product = a.id_product
INNER JOIN tb_sheet d
ON d.id = :id_sheet //received as variable
AND d.id = c.id_sheet
GROUP BY a.id_product
ORDER BY MAX(b.date_purchase)
As requested in the comments, I'm providing a working example here. About the duplicate mark, I don't understand why it's duplicated. I know it's similar, but I think it's quite different because I'm not looking for the last record. I'm looking for the record with the latest date. It may be the first record as long as the date is the latest.
