Unknown column in sub query

Question

I am getting this error:

SET @t_rev := 0.0; 

SET @t_margin := 0.0; 

SET @j_hrs := 0.0; 

SET @t_exp := 0.0; 

SET @j_id := 0; 

SELECT q.quote_id, 
       c.company_name, 
       j.create_date, 
       j.direct_labour_rate, 
       j.overhead_labour_rate, 
       @j_id := j.job_number                                     AS job_number, 
       @j_hrs := (SELECT Sum(Time_to_sec(IF(( end_date <> '00:00:00' 
                                              AND NOT Isnull(end_date) ), 
                                                   Timediff(Concat(end_date, ' ' 
                                                            , 
                                                            end_time), 
                                                   Concat(create_date, ' ', 
                                                   start_time 
                                                   )), Timediff 
                                                    (Concat(create_date, ' ', 
                                                     end_time 
                                                     ), 
                                         Concat(create_date, ' ', start_time) 
                                         ))) / 3600) AS job_time 
                  FROM   production_master 
                  WHERE  job_number = j.job_number)              AS j_time, 
       @t_exp := ( Ifnull((SELECT Sum(amount) 
                           FROM   job_expenses 
                           WHERE  job_id = j.job_number), 0.0) ) AS t_exp, 
       @t_rev := (SELECT Sum(t_rev2) 
                  FROM   (SELECT amount AS t_rev2 
                          FROM   ready_to_ship_detail 
                          WHERE  `job_id` = j.job_number 
                          GROUP  BY invoice_number) AS t_sum)    AS t_rev, 
       @t_margin := ( @t_rev / ( ( ( j.direct_labour_rate 
                                     + j.overhead_labour_rate ) * ( @j_hrs ) ) + 
                                 ( 
                                 @t_exp ) ) )                    AS margin 
FROM   quote_master q 
       LEFT JOIN create_job_master j 
              ON j.quote_id = q.quote_id 
       LEFT JOIN company_master c 
              ON q.company_id = c.company_id 
WHERE  q.create_date BETWEEN '2016-04-01' AND '2016-04-10' 
ORDER  BY c.company_name ASC, 
          q.quote_id DESC 

On the line @t_rev = ... I have j.job_number which is resulted as Unknown column 'j.job_number' in 'where clause' when I execute the query.

I tried this: @j_id = j.job_number and calling job_id=@j_id in the sub-query and it doesn't throw error but full column shows same value as t_rev like this

+-------+
| t_rev |
+-------+
|  236  |
|  236  |
|  236  |
|  236  |
+-------+

How I solve this?

Resulted error: Unknown column 'j.job_number' in 'where clause'

Answer 1

You are accessing alias j in a double nested query, where it is actually can not be accessed.

I have slightly modified your @t_rev sub-query (as you're not using grouped column so you don't need to group it)

@t_rev := (SELECT SUM(amount) AS t_rev2 
              FROM   ready_to_ship_detail 
              WHERE  `job_id` = j.job_number) AS t_sum)    AS t_rev,