MySQL alias for Subquery doesnt work

Question

I write this code into my MySQL Terminal, but MySQL responses with "ERROR 1054 (42S22): Unknown column 'a.SD_ID' in 'on clause'"

I dont know why it doesnt understand my alias.

SELECT  t.TBL_NAME              AS 'TABLE',
t.TBL_TYPE                      AS 'TYPE',
a.COLUMN_NAME                   AS 'COLUMN_NAME',
a.TYPE_NAME                     AS 'TYPE'
   FROM TBLS t
   JOIN
       (SELECT c.TYPE_NAME, c.COLUMN_NAME, c.CD_ID
           FROM COLUMNS_V2 c, SDS s
           WHERE c.CD_ID = s.CD_ID) a
   ON t.SD_ID = a.SD_ID
WHERE t.TBL_TYPE = 'MANAGED_TABLE'
ORDER BY b.TBL_NAME, b.INTEGER_IDX;

I query this in on MySQL on a Ubuntu System.

You forgot to add to your select s.SD_ID or c.SD_ID in the subquery — Sal00m
Why FROM COLUMNS_V2 c, SDS s? Comma-separated joins were made redundant in 1992. That was before MySQL even existed. Use explicit ANSI joins instead (FROM COLUMNS_V2 c JOIN SDS s ON ...). — Thorsten Kettner
And why do you join with SDS at all? You are only selecting values of COLUMNS_V2 in the subquery. What is the purpose of the join? — Thorsten Kettner

Prabhat G Prabhat G · Accepted Answer · 2018-02-26T12:04:07

You need to include SD_ID column inside subquery. Check the commented line below. Also prefer ANSI styled join:

SELECT  
 t.TBL_NAME    AS 'TABLE',
 t.TBL_TYPE    AS 'TYPE',
 a.COLUMN_NAME AS 'COLUMN_NAME',
 a.TYPE_NAME   AS 'TYPE'
FROM TBLS t
  INNER JOIN
     (SELECT c.TYPE_NAME, c.COLUMN_NAME, c.CD_ID -- include either c.SD_ID or s.SD_ID
      FROM COLUMNS_V2 c 
      inner join SDS s
      on c.CD_ID = s.CD_ID
     ) a
   ON t.SD_ID = a.SD_ID
WHERE t.TBL_TYPE = 'MANAGED_TABLE'
ORDER BY b.TBL_NAME, b.INTEGER_IDX;

MySQL alias for Subquery doesnt work

1 Answers