UNION two LEFT JOINs as Virtual Layer in QGIS

Question

In QGIS 2.18, there are two layers, 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 a Virtual Layer 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. What can be a problem?

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

Moreover, I would like to know if I am eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON.

---

References:

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

Answer 1

A bit of theory

Each sub-query must have a separate alias unique within the whole query.

ON clause must refer to a field with that alias, not a name of the inner table which is (in general) invisible out of the sub-query.

---

Solution

SELECT
    *
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

---

Reference:

• Combing two queries in one