MySQL Correlated Subqueries: Subquery cant find table from outer query?
its been sometime since i used corelated subqueries, i am not sure if i am doing this right. in my subquery 2nd last line, i am trying to get node.id
from the outer table. when i try executing the query, i get
Error Code: 1054 Unknown column 'node.id' in 'where clause')
select node.id, node.title, depthLookup.depth
from posts node, (
select count(parent.title) as depth
from posts parent, posts children
where children.lft > parent.lft
and childr开发者_如何学运维en.rgt < parent.rgt
and children.id = node.id
order by parent.lft
) as depthLookup;
It seems you just need to move your expression from clause 'from' to field list
select node.id, node.title,
(
select count(parent.title) as depth
from posts parent, posts children
where children.lft > parent.lft
and children.rgt < parent.rgt
and children.id = node.id
order by parent.lft
) as depthLookup
from posts node;
Or use single-value table like:
select node.id, node.title, depthLookup.depth
from posts node,
(
select count(parent.title) as depth
from posts parent, posts children
where children.lft > parent.lft
and children.rgt < parent.rgt
and children.id = node.id
order by parent.lft
) as depthLookup;
精彩评论