MySQL Correlated Subqueries: Subquery cant find table from outer query?

Question

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 children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup;

burnall · Accepted Answer · 2010-07-28 05:40:02Z

2

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;

answered Jul 28, 2010 at 5:40

burnall

8405 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jiew Meng Over a year ago

moving the subquery to the field list works. isnt your single value table same as mine?

burnall Over a year ago

Exactly same! I didn't catch your idea, initially

Jiew Meng Over a year ago

any idea why i need to move the subquery to the field list? i cannot use from subqueries

Collectives™ on Stack Overflow

MySQL Correlated Subqueries: Subquery cant find table from outer query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related