Hi all:

I have 2 tables: "products" and "pieces"

PRODUCTS

idProd

product

price

PIECES

id

idProdMain

idProdChild

quant

idProdMain and idProdChild are vinculated with the table: "products".

Other considerations is that 1 product can have some pieces and 1 product can be a piece.

Price product equal a sum of quantity * price of all their pieces.

EXAMPLE:

TABLE PRODUCTS (idProd - product - price)

1 - Computer - 300€

2 - Hard Disk - 100€

3 - Memory - 50€

4 - Main Board - 100€

5 - Software - 50€

6 - CDroms 100 un. - 30€

TABLE PIECES (id - idProdMain - idProdChild - Quant.)

1 - 1 - 2 - 1

2 - 1 - 3 - 2

3 - 1 - 4 - 1

WHAT I NEED?

I need update the price of the main product when the price of the product child (piece) is changed. Following the previous example, if I change the price of this product "memory" (is a piece too) to 60€, then product "Computer" will must change his price to 320€

How I can do it using queries?

Already I have tried this to obatin the price of the main product, but not runs. This query not returns any value

SELECT Sum(products.price*pieces.quant) AS Expr1
FROM products LEFT JOIN pieces ON (products.idProd = pieces.idProdChild) AND (products.idProd = pieces.idProdChild) AND (products.idProd = pieces.idProdMain)
WHERE (((pieces.idProdMain)=5));