Home / Questions / 426946
Accepted
Hackerman
Asked: 2022-02-10 05:20:05 +0800 CST 2022-02-10 05:20:05 +0800 CST

Join two queries side by side

  • 772

I have two identical queries but each query is in a different database

SELECT T0.codlote AS Lote1,sum(T0.peso) AS peso1,T1.fecha AS Fecha1
FROM [1_Datos].dbo.tblmvto_lotes T0
INNER JOIN tbllotes T1 ON T1.codlote = T0.codlote
GROUP BY T0.codlote, T1.fecha
ORDER BY T0.codlote
---
SELECT R0.codlote AS Lote2,sum(R0.peso) AS peso2,R1.fecha AS Fecha2
FROM [2_Datos].dbo.tblmvto_lotes R0
INNER JOIN tbllotes R1 ON R1.codlote = R0.codlote
GROUP BY R0.codlote,R1.fecha
ORDER BY R0.codlote

shows me the following

enter image description here

I put them together with the union then I did a subquery, but I don't get the result.

SELECT V1.Lote1, V1.peso1,Fecha1 FROM(
SELECT T0.codlote AS Lote1,sum(T0.peso) AS peso1,T1.fecha AS Fecha1
FROM [1_Datos].dbo.tblmvto_lotes T0
INNER JOIN tbllotes T1 ON T1.codlote = T0.codlote
GROUP BY T0.codlote, T1.fecha
UNION ALL
SELECT R0.codlote AS Lote2,sum(R0.peso) AS peso2,R1.fecha AS Fecha2
FROM [2_Datos].dbo.tblmvto_lotes R0
INNER JOIN tbllotes R1 ON R1.codlote = R0.codlote
GROUP BY R0.codlote,R1.fecha) V1

The result I expect is that the second query stays on the right side of the first, as follows

enter image description here

But I can't do this query, is it with the subquery or how should it be done?

sql

1 Answers

  1. Best Answer

    For this you should not use a UNION, but some JOIN(without knowing which data set has more dates or batches, I would initially recommend using a FULL JOIN).

    SELECT *
FROM (
       SELECT T0.codlote AS Lote1,sum(T0.peso) AS peso1,T1.fecha AS Fecha1
       FROM [1_Datos].dbo.tblmvto_lotes T0
       INNER JOIN tbllotes T1 ON T1.codlote = T0.codlote
       GROUP BY T0.codlote, T1.fecha) x
FULL JOIN (    
           SELECT R0.codlote AS Lote2,sum(R0.peso) AS peso2,R1.fecha AS Fecha2
           FROM [2_Datos].dbo.tblmvto_lotes R0
           INNER JOIN tbllotes R1 ON R1.codlote = R0.codlote
           GROUP BY R0.codlote,R1.fecha) y
     ON x.Lote1 = y.Lote1
     AND x.Fecha = y.Fecha
ORDER BY x.Lote1, y.Lote1;

    Beware that in your initial queries it does not make much sense to group by Date, because it is too small a point when including the time. Perhaps you should convert it only to a date (so that it reaches the day level) and in this way do the JOIN(as it is now, it is most likely that the join will not match any row for this date). It would be like this:

    SELECT *
FROM (
       SELECT T0.codlote AS Lote1,sum(T0.peso) AS peso1,CONVERT(date,T1.fecha) AS Fecha1
       FROM [1_Datos].dbo.tblmvto_lotes T0
       INNER JOIN tbllotes T1 ON T1.codlote = T0.codlote
       GROUP BY T0.codlote, CONVERT(date,T1.fecha)) x
FULL JOIN (    
           SELECT R0.codlote AS Lote2,sum(R0.peso) AS peso2,CONVERT(date,R1.fecha) AS Fecha2
           FROM [2_Datos].dbo.tblmvto_lotes R0
           INNER JOIN tbllotes R1 ON R1.codlote = R0.codlote
           GROUP BY R0.codlote,CONVERT(date,R1.fecha)) y
     ON x.Lote1 = y.Lote1
     AND x.Fecha = y.Fecha
ORDER BY x.Lote1, y.Lote1;
    • 3