Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – sql query shows error by changing alias name

mysql> SELECT 'STATUS', 
              status 
       FROM (SELECT ujm.user_id AS userId, 
                    ujm.job_id AS jobId, 
                    ujm.job_run_name AS jobRunName, 
                    j.response_payload -> '$.isSuccessful' AS isSuccessful, 
                    j.response_payload -> '$.error' AS errors, 
                    j.response_payload -> '$.destinationDetails' AS destinationDetails, 
                    s.name AS statusType 
             FROM user_job_mappings ujm 
             JOIN jobs j ON ujm.job_id = j.id AND j.is_deleted = 0 
             JOIN status_types s ON j.status_type_id = s.id AND s.is_deleted = 0 
             WHERE ujm.user_id = 24301 
             AND ujm.is_deleted = 0 
             order by ujm.job_id limit 1 offset 0
             ) jobs_dataG

ERROR 1054 (42S22): Unknown column ‘status’ in ‘field list’

mysql>  SELECT 'STATUS', 
                status 
        FROM ( SELECT ujm.user_id AS userId, 
                      ujm.job_id AS jobId, 
                      ujm.job_run_name AS jobRunName, 
                      j.response_payload -> '$.isSuccessful' AS isSuccessful, 
                      j.response_payload -> '$.error' AS error, 
                      j.response_payload -> '$.destinationDetails' AS destinationDetails, 
                      s.name AS status 
                FROM user_job_mappings ujm 
                JOIN jobs j ON ujm.job_id = j.id AND j.is_deleted = 0 
                JOIN status_types s ON j.status_type_id = s.id AND s.is_deleted = 0 
                WHERE ujm.user_id = 24301 
                AND ujm.is_deleted = 0 limit 1 offset 0
               ) jobs_dataG
*************************** 1. row ***************************
STATUS: STATUS
status: SUCCESSFUL
1 row in set (0.00 sec)

I have this two queries .
The only difference in both queries is the alias name use for status in inner query but one is giving error I don’t understand why ?

Tags:

2

Answers


  1. 0

    The only difference in both queries is the alias name use for status
    in inner query but one is giving error I don’t understand why ?

    That is the issue. You might see it as just alias name , but those are two different columns names for the outer query.

    The outer query is looking for the status column which doesn’t exists.

    statusType <> status

    Login or Signup to reply.
  2. 0

    When you nest queries, the original table and column names from the inner query no longer matter in the outer query.

    So, in this code, status is not just an alias anymore for the outer query. It is now the actual column name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search