Re: Help rewriting subquery

[Chris Stubben]

Hi,

I assuming you are using a nested set model to count levels in the tree, right? This should work starting at the root node

SELECT child.name, count(parent.name) as level
FROM table child, table parent
WHERE child.left_id BETWEEN parent.left_id and parent.right_id
GROUP BY child.name ORDER BY child.left_id;

If you want to count levels starting at some internal node, use a three table join..

SELECT child.name, count(parent.name) as level
FROM table child, table parent, table subtree
WHERE child.left_id BETWEEN parent.left_id and parent.right_id
AND subtree.name='some internal node'
AND child.left_id between subtree.left_id and subtree.right_id
AND parent.left_id between subtree.left_id and subtree.right_id
GROUP BY child.name ORDER BY child.left_id;


Also, have you tried using the GROUP_CONCAT function (available in 4.1). You can use it to print a single path delimted string.

GROUP_CONCAT(parent.name order by parent.left_id separator '; ') as path


Chris