SQL - Table alias in Left Join

Question

I am trying to write a simple SQL query with Left Join.

This is the query:

SELECT * 
FROM ( 
   SELECT * 
   FROM TRN_IN.COIT AS TRANSMISSIONS
     LEFT JOIN ( 
       SELECT TRNNumber ,COUNT(ID) 
       FROM TRN_IN.COIT AS TOTAL_LINES   
       WHERE DataPiece02 = 'TO'   
       GROUP BY TRNNumber 
   ) ON TRANSMISSIONS.TRNNumber  = TOTAL_LINES.TRNNumber
)

Second table alias is TOTAL_LINES.

When running it, I get an error that TOTAL_LINES is not listed.

The strange thing is, that When trying to run the second query by itself, there is no error:

(SELECT TRNNumber ,COUNT(ID) 
 FROM TRN_IN.COIT AS TOTAL_LINES 
 WHERE DataPiece02 = 'TO' 
 GROUP BY TRNNumber )

Anybody has a clue?

Answer 1

Hope you are assign the AS TOTAL_LINES table alias name in the wrong place.

Placing the table alias name after second sub query will solve your problem.

Can you try the query below:

SELECT *
FROM (
    SELECT *
    FROM TRN_IN.COIT AS TRANSMISSIONS
    LEFT JOIN (
        SELECT TRNNumber
            ,COUNT(ID) AS CountDetails
        FROM TRN_IN.COIT 
        WHERE DataPiece02 = 'TO'
        GROUP BY TRNNumber
        ) AS TOTAL_LINES ON TRANSMISSIONS.TRNNumber = TOTAL_LINES.TRNNumber
    ) AS Result