Xrhrft

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