Need some help please, I want to sum the price in prodprice table and add it to a_cost,b_cost and c_cost in prodcost table where main_item is 00002 in the prodcomb, the 00002 has secondary_item which is 00001,now `00001 in prodbm in main_item have 2 secondary_item
00005 and 00006 these two have price in prodprice and I want to sum there price and add it a_cost,b_cost and c_cost .
the result look like this
+-----+-----------+
| qty | unitprice |
+-----+-----------+
| 2 | 23.50 |
+-----+-----------+
Thank you in advance.
prodprice
+-------+------+
| item | price|
+-------+------+
| 00005 | 5.00 |
+-------+------+
| 00006 | 3.50 |
+-------+------+
prodbm
+-----------+----------------+
| main_item | secondary_item |
+-----------+----------------+
| 00001 | 00005 |
+-----------+----------------+
| 00001 | 00006 |
+-----------+----------------+
prodcomb
+-----------+----------------+-----+
| main_item | secondary_item | qty |
+-----------+----------------+-----+
| 00002 | 00001 | 2 |
prodcost
+-------+--------+--------+--------+-------+
| item | a_cost | b_cost | c_cost | m_cost|
+-------+--------+--------+--------+-------+
| 00001 | 5 | 5 | 5 | 6 |
+-------+--------+--------+--------+-------+