mysql - Upper table visibility in select with multiple subqueries -

i have query:

select b.user_id, b.active users b b.followers_count != (select count(*)      (select u.user_id         user_follow uf,users u,user_follow_request ufr                 uf.following_id = b.user_id ,         uf.following_id = ufr.friend_id ,         ufr.status = 'approved' ,         ufr.user_id = u.user_id , u.user_id != b.user_id , u.active != 0         group u.user_id) a)   , b.active = -1 limit 5; 

it has select user_id's users have different followers_count in column 1 being calculated sql.

but problem i'm receiving error message

error code: 1054. unknown column 'b.user_id' in 'on clause' 

what i'm doing wrong? highly appreciate help.

did mean uf.user_id = b.user_id instead of uf.following_id = b.user_id? anyway, user_follow table for?

agree strawberry, you'd better use explicit joins. easier read , understand. here may have mentioned (note user_follow table not used @ all):

select u.user_id, u.followers_count, if(isnull(uafc.actual_fc),0,uafc.actual_fc) afc   users u     left join (       select u.user_id user_id, count(*) actual_fc         users u           join user_follow_request ufr             on ufr.friend_id = u.user_id           join users fu             on fu.user_id = ufr.user_id         ufr.user_id != u.user_id           , ufr.status = 'approved'           , fu.active != 0         group u.user_id, u.followers_count       ) uafc       on uafc.user_id = u.user_id   u.followers_count != if(isnull(uafc.actual_fc),0,uafc.actual_fc) ; 

there may nicer solution null workaround.

see on sqlfiddle: http://sqlfiddle.com/#!2/71f6c/3