Union all inside the Left outer Join is not fetching all the values, it is just pulling values outsi-CodePudding

I have a question regarding the use of UNION ALL and Left Outer Join. What I am trying to achieve is Get the Project Total costs along with few other fields but at the same time I want few other fields like parts, direct materials from other tables joined with Union All. It is fetching the Project costs and All the fields outside the Left outer join sub-query but for some reason it's not fetching the columns wrapped inside the Join.

SELECT COUNT(EVT_C) AS NumWO,
    PRJ_C AS Project, 
    PRJ_DESC AS ProjectDescription, 
    PRJ_S AS Status, 
    PRJ_DES AS MS, 
    PRJ_UDF03 AS SP, 
    SUM(Cost.Total) AS TotalIntLabour,
FROM R5PRJ
LEFT OUTER JOIN R5E ON EVT_PRJ = PRJ_CODE 
LEFT OUTER JOIN (
        SELECT
            BOO_COST AS Total,
            EVT_PRJ AS Project,
            CAST(BOO_SQLI AS NVARCHAR(400)) AS SystemReference,
            BOO_E AS DateEntered,
            'Part' AS RType,
            'Direct Materials' AS Type
        FROM R5BOO
        LEFT OUTER JOIN R5E ON EVT_C = BOO_E 
        WHERE EVT_PRJ IS NOT NULL
    UNION ALL
        SELECT 
            TRL_PRICE*TRL_QTY AS Total,
            EVT_PRJ AS Project,
            TRL_T ' ' TRL_P AS SystemReference,
            TRL_D AS DateEntered,
            'Part' AS RType,
            'Direct Materials' AS Type
        FROM R5TR
        LEFT OUTER JOIN R5E ON TRL_E = EVT_C
        LEFT OUTER JOIN R5O ON OBJ_C = EVT_OBJ
        WHERE TRL_IO = 0 AND TRL_R IN ('RECV','RETN')
    UNION ALL 
        SELECT 
            TRL_PRICE*TRL_QTY AS Total,
            EVT_PRJ AS Project,
            TRL_T ' ' TRL_P AS SystemReference,
            TRL_D AS DateEntered,
            'Part' AS RType,
            'Direct Materials' AS Type
        FROM R5TR
        LEFT OUTER JOIN R5E ON TRL_E = EVT_C
        LEFT OUTER JOIN R5O ON OBJ_C = EVT_OBJ
        WHERE TRL_E IS NOT NULL AND TRL_R = 'I' AND TRL_IO = -1
    ) AS Cost ON Cost.Project = PRJ_C
GROUP BY PRJ_C,PRJ_D,PRJ_S,PRJ_D,PRJUDF03,PRJ_ACTBUD,
    PRJ_ORIGBUD,PRJ_UDF01,PRJ_CLASS,PRJ_C

CodePudding user response：

it's not fetching the columns wrapped inside the Join.

You didn't SELECT them in the outer query.

SQL operates on blocks of data. A table is a block of data that is fed into a FROM. The output of a query is also a block of data that can be fed into a FROM.

Person table has 3 columns; Name, Birthday, Height.

When you write:

SELECT name 
FROM person

You get just Name, even though the table has two other columns also

When you write:

SELECT name 
FROM (
  SELECT name, height 
  FROM person
)x

You do not suddenly get Name and Height. You said you only wanted Name in the final executed SELECT (the topmost one). Just because you mentioned Height in the inner query does not mean it appears as an output of the outer query. The output of the inner query is fed into the outer query, and the out query doesn't select the Height column.. just like it didn't when the table name was used in

SELECT name    --doesn't mention height
FROM person    --even though this has height

It's all just "input blocks of data" (in this case the Person table) and "output blocks of data" (in this case one output fed back to you)

In this query:

SELECT name            --also doesn't mention height
FROM (
  SELECT name, height  --even though this has height
  FROM person
)x

We have "input data block of 3 columns from table person becomes 2 column wide output", that becomes "input data block of 2 columns becomes 1 column wide output" that is returned to you

In an outer query you can only reference the columns selected by an inner/sub query, and you reference them using the alias you gave to the sub query. The first query in any set of unions defines the column names for the entire union set

It's like as if the sub query is run and temporarily turned into a table for the duration of the query, thus:

SELECT sq.x
FROM (
  SELECT name as x
  FROM Person

  UNION ALL

  SELECT building_name
  FROM address
) sq

Here you can see the sub query that does the union has been aliased as sq. The first query takes a person name and aliases it as x. The second query of the union pulls a building name out of the addresses table but this name has no effect on what the column will be called. The column is called x thanks to the first query in the union set

Thus you end up with a column you refer to as sq.x which is full of a mix of people and building names

In the outer query you cannot refer to any names of any columns or tables in the sub query; the sub query is run and the columns it selects become the columns in the block of data it is aliased as. Anything not selected is gone. If you need to use something you must select it:

SELECT sq.x
FROM (
  SELECT name as x, age as y
  FROM Person

  UNION ALL

  SELECT building_name, YEAR(GetUtcDate()) - building_built_year
  FROM address
) sq
WHERE 
  sq.y > 50

This gets all people or buildings older than 50 years: the person query aliased age as y, the outer query used y. There is no alias given to the formula that caluctaes the building age; it goes in the y column thanks to the first query