WHERE (child.lft BETWEEN Yellow.lft AND Yellow.rgt)
in other words The SQL give the meaning to child.lft after it picks up all the nodes between Yellow.lft AND Yellow.right. I understand that’s the function right there whatever is inside the fixed pointers Yellow.lft AND Yellow.rgt then contain it within the child.lft general pointer or called child.lft? hope i am right.
Yes I just realized it will include even the sub-parent node here called Yellow which contain Banana inside itself. And as you Said BETWEEN is inclusive I realized that it will include it in there, which in the case I only want its child or Yellow child to display. Well in the statement above which only display local nodes meaning only banana.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM categories AS node, categories AS parent, categories AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM categories AS node,
categories AS parent
WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
AND node.name = :node_name
GROUP BY node.name
ORDER BY node.left_node
)AS sub_tree
WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
AND node.left_node BETWEEN sub_parent.left_node AND sub_parent.right_node
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.left_node
In the directions it say to take < symbol out if I want the root node out, in this case Yellow 0 is out and it only display Banana but with an annoying 1 next to it, I was wondering how can I take out the Level number 1. Lol.
with the symbol of < at the HAVING clause display like with the parent Yellow with a death of 0
[QUOTE]
name – Yellow
depth – 0
name – Banana
depth – 1
[/QOUTE]
without the symbol of < at the HAVING clause display without the parent Yellow with a death of 0
[QUOTE]
name – Banana
depth – 1
[/QOUTE]
wondering how to take out that depth number.
It seems like the SELECT within first SELECT is selecting a node within another node. am I correct, WEll that very interesting, it means it can go infinite.
If it possible to go through three SELECT containing eachother to obtain a node a third depth level and after digging to a third level only display the third level? as in the query above where it goes one level down with the options of displaying depth 0 YELLOW and depth 1 BANANA and also displaying only Banana the 1 depth level and omitting the depth 0?
Just want to make if the same theory with of digging into one depth can apply to digging into a two or three depth query.