Add value of a table related to itself

Navigation
0

Hello I have a question about how to make a query in sql to add the total time it takes to produce the product.

The diagram is a table of products that has sub-products, the design is an NN relationship with itself so I made an auxiliary table to store what would be the id of the parent product with the id of the child product . I keep that relation in the product_x_productos table with the number of child products in the relationship.

These would be the tables 

productos(PK idproducto, tiempo_produccion INT)                
productos_x_productos(PK idproducto_padre (FK productos.idproducto), PK idproducto_hijo (FK productos.idproducto), cantidad INT)

A product has a time that takes to be manufactured, this product in turn can be composed of several products that also have their time of manufacture and in addition to the amount of each sub-product of the same type.

I would like to know how to make the query that sum total time to manufacture the product what would be total_time = total_time + (total_time_child) * amount (that for each sub-product you have)

I do not know if it could be done in a single consultation, it would be the best, but if you can not if someone could guide me on how to do it in an SP. The database engine is MYSQL.

    
asked by Diego A 13.10.2017 в 14:04
source

2 answers

0

I already solved it in the following way: 

SELECT
(SELECT SUM(pp.cantidad_productos * p.tiempo_produccion)
FROM productos_x_productos pp
JOIN productos p ON
p.idproducto = pp.productos_idproducto_hijo
WHERE pp.productos_idproducto_padre = $idproducto) +
(SELECT tiempo_produccion
FROM productos
WHERE idproducto = $idproducto) AS tiempo_total

Thanks for the help!

    
answered by 13.10.2017 / 15:15
source
0

How would I do it? In the following way ...

We know that the total production time of a product is: 

SELECT tiempo_produccion FROM productos WHERE idProducto = "Id";

The last Id would be the value that you would introduce to the query.

Now the total time of the son would be: 

SELECT SUM(tiempo_producción) AS tiempo_hijo FROM productos_x_productos WHERE idproducto_padre = "Id";

SELECT SUM(cantidad) AS Cantidad FROM productos_x_productos WHERE idproducto_padre = "Id";

Remember that the quoted Id is where you should put the Id of the product you want to calculate.

What would remain is to perform the arithmetic operation of the results we have obtained in the consultations.

    
answered by 13.10.2017 в 14:28
Validation duplicate registration in form with Javascript Redirect log lines to a file, without keeping them in the main log