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

Answer 1

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_