MySQL “Unknown column” in subquery

Question

SELECT  
hi_proins0.PROC_INST_ID_  processId,
act_re_procdef.NAME_ processName,
(
     #Unknown column 'hi_proins0.PROC_INST_ID_' in 'where clause'
   SELECT IFNULL(ct,0) FROM (
       SELECT COUNT(*) FROM act_hi_taskinst hi_task1
            INNER JOIN act_hi_procinst hi_proins1
                ON hi_task1.PROC_INST_ID_ = hi_proins1.PROC_INST_ID_
            WHERE  hi_proins1.PROC_INST_ID_ = hi_proins0.PROC_INST_ID_
                AND hi_task1.ASSIGNEE_ != hi_proins1.START_USER_ID_
                AND hi_task1.ASSIGNEE_ = hi_task0.ASSIGNEE_
            GROUP BY hi_task1.ASSIGNEE_
       ) ti

) approvalNodeCount,
(
   ...
) spendTime FROM 
  act_hi_taskinst hi_task0

LEFT JOIN act_hi_procinst hi_proins0 ON hi_proins0.PROC_INST_ID_ = hi_task0.PROC_INST_ID_
INNER JOIN act_re_procdef ON act_re_procdef.ID_ = hi_proins0.PROC_DEF_ID_ 
GROUP BY hi_proins0.PROC_INST_ID_

And it throw Unknown column 'hi_proins0.PROC_INST_ID_' in 'where clause' on

 SELECT IFNULL(ct,0) FROM (
   ...
 ) ti

and ifnull is not work on above

how to solve it? :D

You need to move the where clauses to the bottom of your query. — Nix, Jan 09 '13 at 02:31
You only join tables `act_hi_taskinst` (hi_task1) and `act_hi_procinst` (hi_poins1) but no table or alias with name `hi_proins0` in your sub-query. Maybe you need another join with a table (but you seem to already have act_hi_procinst) or to change in the sub-query the `WHERE hi_proins1.PROC_INST_ID_ = hi_proins0.PROC_INST_ID_ AND hi_task1.ASSIGNEE_ != hi_proins1.START_USER_ID_` to `WHERE hi_task1.ASSIGNEE_ != hi_proins1.START_USER_ID_` — JScoobyCed, Jan 09 '13 at 02:34
You will have another similar error after you fix this one. Because in the `AND hi_task1.ASSIGNEE_ = hi_task0.ASSIGNEE_` clause you use the `hi_task0` reference but it doesn't exist either. — JScoobyCed, Jan 09 '13 at 02:36

score 0 · Accepted Answer · answered Jan 09 '13 at 05:27

Try this:

SELECT hi_proins0.PROC_INST_ID_  processId, act_re_procdef.NAME_ processName,
       IFNULL((SELECT COUNT(*) FROM act_hi_taskinst hi_task1
               INNER JOIN act_hi_procinst hi_proins1 ON hi_task1.PROC_INST_ID_ = hi_proins1.PROC_INST_ID_
               WHERE hi_proins1.PROC_INST_ID_ = hi_proins0.PROC_INST_ID_    AND 
                     hi_task1.ASSIGNEE_ != hi_proins1.START_USER_ID_ AND hi_task1.ASSIGNEE_ = hi_task0.ASSIGNEE_
               GROUP BY hi_task1.ASSIGNEE_
            ), 0 ) approvalNodeCount,
(
   ...
) spendTime 
FROM act_hi_taskinst hi_task0
LEFT JOIN act_hi_procinst hi_proins0 ON hi_proins0.PROC_INST_ID_ = hi_task0.PROC_INST_ID_
INNER JOIN act_re_procdef ON act_re_procdef.ID_ = hi_proins0.PROC_DEF_ID_ 
GROUP BY hi_proins0.PROC_INST_ID_

@Bodhi'maN You have used column (hi_proins0.PROC_INST_ID_) in nested subquery and MySQL doesn't support nested subquery in SELECT part It allows only for subquery. — Saharsh Shah, Jan 10 '13 at 04:57

MySQL “Unknown column” in subquery

1 Answers1