Combing two queries in one

Question

I have two tables, e.g. "Locations" and "Connections"

"Locations" has values

Id | Dimension
---------------    
 1 |    4
 2 |    8
 3 |    2

"Connections" maintains attributes

Origin | Destination | Value | Distance_KM
-------------------------------------------
   1   |      2      |  500  |     30
   1   |      3      |  100  |     20
   2   |      1      |  100  |     10
   2   |      3      |  300  |     10
   3   |      1      |  100  |     40

I want to create an output with the following Attribute Table. Where "In" correspond to "Destination" from "Connections" and "Out" to "Origin" accordingly.

Id | Dimension | In_Value | In_Count | In_Dist | Out_Value | Out_Count | Out_Dist
----------------------------------------------------------------------------------  
 1 |     4     |    200   |     2    |    50   |    600    |    2      |     50
 2 |     8     |    500   |     1    |    30   |    400    |    2      |     20 
 3 |     2     |    400   |     2    |    30   |    100    |    1      |     40

I can achieve the result that I strive for separately with two queries.

Query 1

SELECT C.Destination, SUM(C.Value) AS In_Value, COUNT(C.Destination) AS In_Count, SUM(C.Distance_KM) AS In_Dist
FROM Connections AS C
GROUP BY C.Destination

Query 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

Nevertheless, there should be only one query that solves my issue, is not it? I tried this but no success.

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.Out_Dist
FROM   Locations AS L
LEFT   JOIN (
             SELECT C.Destination,
                    SUM(C.Value) AS In_Value,
                    COUNT(C.Destination) AS In_Count,
                    SUM(C.Distance_KM) AS In_Dist
             FROM Connections AS C
             GROUP BY C.Destination
        ) ON L.Id = C.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count, 
                  SUM(C.Origine_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
        ) ON L.Id = C.Origin

Basically, I do not know if I eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON, am I?

---

References:

• Two SQL LEFT JOINS produce incorrect result
• Multiple left joins on multiple tables in one query

Answer 1

SELECT L.Id AS Id, L.Dimension AS Dimension, 
       sq1.In_Value, sq1.In_Count, sq1.In_Dist, 
       sq2.Out_Value, sq2.Out_Count, sq2.Out_Dist
FROM Locations AS L
LEFT JOIN (
           SELECT C.Destination, 
                  SUM(C.Value) AS In_Value,  
                  COUNT(C.Destination) AS In_Count,  
                  SUM(C.Distance_KM) AS In_Dist
           FROM Connections AS C
           GROUP BY C.Destination
         ) AS sq1 ON L.Id = sq1.Destination
LEFT JOIN (
           SELECT C.Origin,  
                  SUM(C.Value) AS Out_Value,  
                  COUNT(C.Origin) AS Out_Count,  
                  SUM(C.Distance_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
         ) AS sq2 ON L.Id = sq2.Origin

fiddle

Answer 2

Another possibility is to join Connections to Locations on Locations.Id IN (Connections.Origin, Connections.Destination) and perform a series of conditional aggregations depending on which of the two columns matched.

This is what I have in mind:

SELECT
  l.Id
, l.Dimension
, SUM(CASE l.Id WHEN c.Destination THEN c.Value       END) AS In_Value
, SUM(CASE l.Id WHEN c.Destination THEN 1 ELSE 0      END) AS In_Count
, SUM(CASE l.Id WHEN c.Destination THEN c.Distance_KM END) AS In_Dist
, SUM(CASE l.Id WHEN c.Origin      THEN c.Value       END) AS Out_Value
, SUM(CASE l.Id WHEN c.Origin      THEN 1 ELSE 0      END) AS Out_Count
, SUM(CASE l.Id WHEN c.Origin      THEN c.Distance_KM END) AS Out_Dist
FROM
  Locations AS l
  LEFT JOIN Connections AS c ON l.Id IN (c.Origin, c.Destination)
GROUP BY
  l.Id
, l.Dimension
;

Note: the counts (In_Count, Out_Count) have been implemented using the SUM function, which is merely for consistency with the rest of the code. You can use COUNTs instead, which would look almost the same, just without the ELSE 0 bit:

...
, COUNT(CASE l.Id WHEN c.Destination THEN 1 END) AS In_Count
...
, COUNT(CASE l.Id WHEN c.Origin      THEN 1 END) AS Out_Count
...

Despite looking arguably simpler, this approach may not necessarily be faster, especially on larger datasets. However, in case performance is not an issue, this option might look more readable, and therefore more maintainable.

The solution can be tested online with the help of this fiddle (borrows the test setup from Akina's fiddle).