1

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?

Yaniv Ben-Malka
  • 47
  • 3
  • 10

3 Answers3

3

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
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
1

TOTAL_LINES must be the alias of the whole select, not on the table in from clause. Change To:

SELECT * FROM ( SELECT * FROM TRN_IN.COIT AS TRANSMISSIONS

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

ON TRANSMISSIONS.TRNNumber  = TOTAL_LINES   .TRNNumber)
Jens
  • 67,715
  • 15
  • 98
  • 113
1

Try this..

SELECT TOTAL_LINES.TRNNumber,COUNT(TOTAL_LINES.ID) FROM TRN_IN.COIT TRANSMISSIONS LEFT JOIN TRN_IN.COIT TOTAL_LINES 
ON TRANSMISSIONS.TRNNumber  = TOTAL_LINES.TRNNumber
WHERE TOTAL_LINES.DataPiece02 = 'TO'   GROUP BY TOTAL_LINES.TRNNumber;
Jens
  • 67,715
  • 15
  • 98
  • 113
Senthil
  • 55
  • 8