Alejandro Muñoz
Reputation: 7
Join Tables or union Tables
I am trying to join two selections in the same query. I tried a union but I need the registers to apply in the same rows.
My select 1 is:
SELECT
b.cod_articulo,
b.nom_articulo,
sum(a.cantidad) AS 'Cantidad',
sum(a.cantidad * b.conv_art_prec) AS 'Total Peso',
b.cod_uni_pesoart AS 'Unidad'
FROM
cpf_stockaux a
LEFT OUTER JOIN
ct_articulos b
ON
a.cod_articulo=b.cod_articulo
WHERE
b.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND YEAR(a.fec_doc)='2021'
AND MONTH(a.fec_doc)='05'
GROUP BY b.cod_articulo, b.nom_articulo, b.cod_uni_pesoart
the result of that select is:
cod_articulo|nom_articulo |Cantidad |Total Peso |Unidad
40101 |SEMOLIN (BOLSA 25 KG.) |17998.00000 |449950.00000000000 |Kg
4201 |HARINA DE TRIGO 0000 PARA TAPAS KG.(BOLSA 25 KG.) |13280.00000 |332000.00000000000 |Kg
451 |HARINA DE TRIGO 000 CON MEJORADORES KG.A GRANEL |1094484.88000 |1094484.88000000000 |Kg
501 |HARINA 00 CON MEJORADORES KG. A GRANEL |2097668.36000 |2097668.36000000000 |Kg
551 |HARINA 0 CON MEJORADORES KG. A GRANEL |4859169.16000 |4859169.16000000000 |Kg
5510 |HARINA 0 SIN ADITIVACION KG. GRANEL |2138322.64050 |2138322.64050000000 |Kg
5613 |AFRECHILLO DE TRIGO KG. A GRANEL |6850287.25000 |6850287.25000000000 |Kg
562 |SALVADO DE TRIGO KG.(BOLSA 25 KG.) |1380.00000 |34500.00000000000 |Kg
5625 |SALVADO KG. (BOLSA 20 KG.) |132.00000 |2640.00000000000 |Kg
58201 |HARINA INTEGRAL KG. (BOLSA 25 KG.) |64657.00000 |1616425.00000000000 |Kg
My second select:
SELECT
c.cod_articulo,
c.nom_articulo,
sum(b.total) AS 'Cantidad No Conf.',
sum(b.total * c.conv_art_prec) AS 'Total No Conf. Peso',
FROM
cpt_parteprod b
LEFT OUTER JOIN
ct_articulos c
ON
b.cod_articulo=c.cod_articulo
WHERE
c.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND b.cod_doc = 'ppro'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND b.formulario = 'TprdParteEMoliNC'
GROUP BY c.cod_articulo, c.nom_articulo, c.cod_uni_pesoart
the result of that select is:
cod_articulo|nom_articulo |Cantidad No Conf. |Total No Conf. Peso |Unidad
40101 |SEMOLIN (BOLSA 25 KG.) |6.00000 |150.00000000000 |Kg
4201 |HARINA DE TRIGO 0000 PARA TAPAS KG.(BOLSA 25 KG.) |189.00000 |4725.00000000000 |Kg
58201 |HARINA INTEGRAL KG. (BOLSA 25 KG.) |3.00000 |75.00000000000 |Kg
And what i'm trying to do:
cod_articulo|nom_articulo |Unidad |Total |Unidad |Cantidad No Conf. |Total No Conf. Peso |Unidad
40101 |SEMOLIN (BOLSA 25 KG.) |17998.00000 |449950.00000000000 |Kg |6.00000 |150.00000000000 |Kg
4201 |HARINA DE TRIGO 0000 PARA TAPAS KG.(BOLSA 25 KG.) |13280.00000 |332000.00000000000 |Kg |189.00000 |4725.00000000000 |Kg
451 |HARINA DE TRIGO 000 CON MEJORADORES KG.A GRANEL |1094484.88000 |1094484.88000000000 |Kg |0 |0 |Kg
501 |HARINA 00 CON MEJORADORES KG. A GRANEL |2097668.36000 |2097668.36000000000 |Kg |0 |0 |Kg
551 |HARINA 0 CON MEJORADORES KG. A GRANEL |4859169.16000 |4859169.16000000000 |Kg |0 |0 |Kg
5510 |HARINA 0 SIN ADITIVACION KG. GRANEL |2138322.64050 |2138322.64050000000 |Kg |0 |0 |Kg
5613 |AFRECHILLO DE TRIGO KG. A GRANEL |6850287.25000 |6850287.25000000000 |Kg |0 |0 |Kg
562 |SALVADO DE TRIGO KG.(BOLSA 25 KG.) |1380.00000 |34500.00000000000 |Kg |0 |0 |Kg
5625 |SALVADO KG. (BOLSA 20 KG.) |132.00000 |2640.00000000000 |Kg |0 |0 |Kg
58201 |HARINA INTEGRAL KG. (BOLSA 25 KG.) |64657.00000 |1616425.00000000000 |Kg |3.00000 |75.00000000000 |Kg
for the cases that I have 0 it is clear that I use a CASE
Upvotes: 0
Views: 54
Answers (3)
Daniel Tschick
Reputation: 56
You could try this way:
WITH tab1 AS
(
<your select 1 here>
),
tab2 AS
(
<your select 2 here>
)
SELECT *
FROM
tab1
LEFT JOIN
tab2
ON
tab1.cod_articulo = tab2.cod_articulo
Upvotes: 0
Derek Macrae
Reputation: 49
I think you need a left outer join between the two selects if I understand it correctly, like:
SELECT
stk.cod_articulo,
stk.nom_articulo,
stk.Cantidad,
stk.TotalPeso,
stk.Unidad,
IFNULL(nc.CantidadNoConf, 0) AS CantidadNoConf,
IFNULL(nc.TotalNoConfPeso,0) AS TotalNoConfPeso
FROM
(
SELECT
b.cod_articulo,
b.nom_articulo,
sum(a.cantidad) AS Cantidad,
sum(a.cantidad * b.conv_art_prec) AS TotalPeso,
b.cod_uni_pesoart AS Unidad
FROM
cpf_stockaux a
LEFT OUTER JOIN
ct_articulos b
ON
a.cod_articulo=b.cod_articulo
WHERE
b.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND YEAR(a.fec_doc)='2021'
AND MONTH(a.fec_doc)='05'
GROUP BY b.cod_articulo, b.nom_articulo, b.cod_uni_pesoart
) AS stk
LEFT OUTER JOIN
(
SELECT
c.cod_articulo,
sum(b.total) AS CantidadNoConf,
sum(b.total * c.conv_art_prec) AS TotalNoConfPeso,
FROM
cpt_parteprod b
LEFT OUTER JOIN
ct_articulos c
ON
b.cod_articulo=c.cod_articulo
WHERE
c.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND b.cod_doc = 'ppro'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND b.formulario = 'TprdParteEMoliNC'
GROUP BY c.cod_articulo, c.nom_articulo, c.cod_uni_pesoart
) AS nc ON stk.cod_articulo = nc.cod_articulo
ORDER BY stk.cod_articuloI don't know what database you are using, this was written for MySQL/MariaDB. If it is SQLServer use "ISNULL" instead of "IFNULL".
Upvotes: 0
Gigliotti
Reputation: 24
The best way to do this Join is using "FULL OUTER JOIN" Image explain full outer join, because you need full information of both Data, and in this case you can use this code:
Select
A.cod_articulo,
A.nom_articulo,
A.Cantidad,
A.Total_Peso,
A.Unidad,
B.Cantidad_No_Conf,
B.Total_No_Conf_Peso
from (SELECT
b.cod_articulo,
b.nom_articulo,
sum(a.cantidad) AS 'Cantidad',
sum(a.cantidad * b.conv_art_prec) AS Total_Peso,
b.cod_uni_pesoart AS 'Unidad'
FROM
cpf_stockaux a
LEFT OUTER JOIN
ct_articulos b
ON
a.cod_articulo=b.cod_articulo
WHERE
b.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND YEAR(a.fec_doc)='2021'
AND MONTH(a.fec_doc)='05'
GROUP BY b.cod_articulo, b.nom_articulo, b.cod_uni_pesoart) as A
FULL OUTER JOIN
(SELECT
c.cod_articulo,
c.nom_articulo,
sum(b.total) AS Cantidad_No_Conf,
sum(b.total * c.conv_art_prec) AS Total_No_Conf_Peso,
FROM
cpt_parteprod b
LEFT OUTER JOIN
ct_articulos c
ON
b.cod_articulo=c.cod_articulo
WHERE
c.cod_articulo IN ('40101','411','4112','4134','4135','4201','433','434','446','451','467','473','480','501','540','551','5510','562','5625','58201','635','5613')
AND b.cod_doc = 'ppro'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND YEAR(b.fec_doc)='2021'
AND MONTH(b.fec_doc)='05'
AND b.formulario = 'TprdParteEMoliNC'
GROUP BY c.cod_articulo, c.nom_articulo, c.cod_uni_pesoart) as B
ON A.cod_articulo = B.cod_articulo
Upvotes: 1