MySQL - Subquery in SELECT raising "unkown colum" on outer reference-CodePudding

I am trying to get a field which is calculated by a subquery. I found about 400 posts at SO that state that you can use the outer ID in a select subquery clause unless you are trying to use it in a join (what I don't).

Here's my query:

SELECT (SELECT group_concat(ct.NAME) 
        FROM core_tagobject cto 
        JOIN core_tag ct ON ct.id=cto.tag_id 
         AND cto.context="tags_working_on" 
         AND cto.object_id=u.id) AS "tag_list"
FROM auth_user u

I always get back SQL error (1054) - unknown column 'u.id' in 'on clause'.

What am I doing wrong?

Thx!

CodePudding user response：

I guess you want below sql

SELECT group_concat(ct.NAME) AS `tag_list`
FROM core_tagobject cto JOIN core_tag ct ON ct.id=cto.tag_id 
  JOIN auth_user u ON cto.object_id=u.id
WHERE cto.context="tags_working_on"

CodePudding user response：

With some data examples would be better to produce a proper solution, but could you try:

SELECT tag_list.*
FROM  auth_user u 
INNER JOIN (
            SELECT group_concat(ct.NAME) 
            FROM core_tagobject cto 
            JOIN core_tag ct ON ct.id=cto.tag_id 
            AND cto.context="tags_working_on" 
           ) AS tag_list on tag_list.object_id=u.id ;