subselect to join

[josoroma]

I was studying nested set model:

MySQL AB :: Managing Hierarchical Data in MySQL

 

Everything goes cool, because of my mysql version, now im having nightmares trying to pass the following subselect to joins, any help is welcome:

 

 

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth

FROM nested_category AS node,

nested_category AS parent,

nested_category AS sub_parent,

(

SELECT node.name, (COUNT(parent.name) - 1) AS depth

FROM nested_category AS node,

nested_category AS parent

WHERE node.lft BETWEEN parent.lft AND parent.rgt

AND node.name = 'PORTABLE ELECTRONICS'

GROUP BY node.name

ORDER BY node.lft

)AS sub_tree

WHERE node.lft BETWEEN parent.lft AND parent.rgt

AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt

AND sub_parent.name = sub_tree.name

GROUP BY node.name

HAVING depth <= 1

ORDER BY node.lft;

 

 

Thanks in advanced.

[Illusion]

COUNT(parent.name) - (sub_tree.depth + 1) is not making any sense to me. Is sub_tree.depth is a constant.

[josoroma]

Is the same query at the mysql website tutorial about:

Managing Hierarchical Data in MySQL

But this post doesnt let me to write the URL.

 

Is part of the subselect:

...

(

SELECT node.name, (COUNT(parent.name) - 1) AS depth

FROM nested_category AS node,

nested_category AS parent

WHERE node.lft BETWEEN parent.lft AND parent.rgt

AND node.name = 'PORTABLE ELECTRONICS'

GROUP BY node.name

ORDER BY node.lft

)AS sub_tree

...

 

Any help is welcome with the sql conversion from subselect to join.

 

Thanks.

[Illusion]

as per my knowledge

(COUNT(parent.name) - 1)  will work as 1 is a constant

 

and

 

(COUNT(parent.name) - (sub_tree.depth + 1))  won't work as sub_tree.depth is a variable(I mean it varies).