-1

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.

  • Why LEFT joins? – Akina Oct 17 at 16:29
  • @Akina I don't know. I don't know much about mysql. Any suggestions? – CelsomTrindade Oct 17 at 16:50
0

Try using the funtion max for the fields value and date, also apply `GROUP BY to id_item

For example:

SELECT a.id_item, a.value , max(b.date) as 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
GROUP BY a.id_item, a.value
HAVING b.date=MAX(b.date)
  • It's returning the latest date and the greatest price but they are not correlated. For example, I got this result: 34.650 | 2019-10-12 but this value belongs to a purchase made on 2019-08-16. If using only the MAX(b.date) it also gets the latest date without correlation with the purchase. – CelsomTrindade Oct 18 at 12:35
  • so, using only MAX(a.value) – Juan Carlos Hdz Oct 18 at 14:31
  • I don't want the greatest value. I want the value on the latest purchase. – CelsomTrindade Oct 18 at 14:33
  • try add the function HAVING where date is equal to max(date) – Juan Carlos Hdz Oct 18 at 15:04
  • Still nothing. I've made this fiddle to test online with the data sqlfiddle.com/#!9/444f83/1 – CelsomTrindade Oct 18 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.