Add 4 columns of 4 different tables

Question

Add 4 columns of 4 different tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I am doing an inventory system in which I must generate a table with a view called General Inventory which must add the values of four existing warehouses. In the database I have the four tables of which I must only add the number of pieces that there are per product of each of the tables and show the total in general inventory. But I still can not get it.

$xcrud = Xcrud::get_instance();
$xcrud->table('inventario_general');
    $xcrud->relation('id_producto', 'producto', 'id_producto', 'nombre_producto');
    $xcrud->relation('id_categoria', 'categoria', 'id_categoria', 'nombre_categoria');
     $xcrud->subselect('cantidad_piezastj','SELECT cantidad_piezas FROM inventario_real WHERE id_producto = {id_producto}');
     $xcrud->subselect('cantidad_piezascb','SELECT cantidad_piezas FROM inventario_cabos WHERE id_producto = {id_producto}');
     $xcrud->subselect('cantidad_piezassd','SELECT cantidad_piezas FROM inventario_sd WHERE id_producto = {id_producto}');
     $xcrud->subselect('cantidad_piezasmx','SELECT cantidad_piezas FROM inventario_mxl WHERE id_producto = {id_producto}');
        $xcrud->subselect('precio_compra','SELECT precio_compra FROM inventario_real  WHERE id_producto = {id_producto}');
            $xcrud->subselect('total_vendido','{total_piezas}*{precio_compra}'); 
           // $xcrud->subselect('total_piezas','{cantidad_piezasmx} + {cantidad_piezastj} + {cantidad_piezascb} {cantidad_piezassd}');
            //$xcrud->subselect('total_piezass','{cantidad_piezascb} + {cantidad_piezassd} ');
           $xcrud->subselect('total_piezas', 'SELECT SUM(cantidad_piezas) FROM (SELECT sum(cantidad_piezas) cantidad_piezas FROM inventario_real UNION ALL SELECT sum(cantidad_piezas) FROM inventario_mxl UNION ALL SELECT sum(cantidad_piezas) FROM inventario_cabos  WHERE id_producto={id_producto}');

The tables are:

bodegaA : the columns are id_producto , cantidad_piezasa , preciocompra , totaldinero ,
bodegaB : the columns are id_producto , cantidad_piezasa , preciocompra , totaldinero ,
bodegaC : the columns are id_producto , cantidad_piezasa , preciocompra , totaldinero ,
bodegaD : the columns are id_producto , cantidad_piezasa , preciocompra , totaldinero .

Of which I have to add the cantida_piezas of the products that are in each table and put it in a general inventory

sql

asked by user5406 14.04.2016 в 14:18

source

2 answers

WebService REST with Genexus Torres de hanoi

score 1 · Answer 1

1

for this Union all is used

Select Sum(cantidad) cantidad_total from (
select sum(cantidad) cantidad from tabla 1
union all
select sum(cantidad) from tabla 2
union all 
select sum(cantidad) from tabla 3) as Total

answered by 14.04.2016 в 14:38

score 0 · Answer 2

to start I think that the DB is badly designed, it should not have but a table by but you have noticed the four tables have the same fields, on the other hand you should use a stored procedure (I do not know which BD manager you are using for to give example), said procedure that returns all products with their totals and of which warehouse they are or all the products without importing warehouse. I hope I have helped, good day. And the query would be something like that

SELECT SUM(cantidad),
Producto, bodega
FROM OrderDetails group by producto,bodega;