I'm trying to calculate the cost to make one product, for this I have a table with the data sheet to build each product. So, for example, to make the Product A
I'll use item a
, item d
and item g
.
Later on I need to calculate this cost based on the last purchase made for each of the 3 items, a
, d
and g
so I know how much it costs to make Product A
*tb_purchase*
id | date | total_value | ...
*tb_purchase_item*
id_purchase | id_item | quantity | value
*tb_sheet*
id | product | total_cost
1 | "Product A" | 47.00
*tb_sheet_item*
id_sheet | id_item | quantity
1 | 3 | 1.3
1 | 5 | 0.8
1 | 9 | 2.0
To get these values I'm trying This is the query I'm doing to get only the items that is referent to the Product A
:
SELECT a.id_item, a.value, b.date
FROM tb_purchase_item a
LEFT JOIN tb_purchase b ON b.id = a.id_purchase
LEFT JOIN tb_sheet_item c ON c.id_item = a.id_item
LEFT JOIN tb_sheet d ON d.id = c.id_sheet
WHERE d.id = :id_sheet
This query is returning only the items I need correctly, but it's returning all the purchases made for those items. I need only tha last one made.
So, instead of this result:
id_item | value | date
3 | 14.89 | 2019-10-12
5 | 07.90 | 2019-10-09
9 | 10.90 | 2019-10-10
I'm getting results like this:
id_item | value | date
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
What should I do to return only the last record for each item? I tried using ORDER BY
and then GROUP BY
but it return a syntax error.