Help with writing a SQL query for Nested Sets

Question

I'm storing a tree in a DB using nested sets. The table's fields are id, lft, rgt, and name.

Given a node ID, I need to find all of its direct children(not grandchildren) that are themselves leaf nodes.

Answer 1

This page gives a great example of how to use Nested Sets, and gives examples of many common queries, including this one.

here's how to find the immediate children of a node:

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 = '**[[MY NODE]]**'
        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;

and then combine that with the fact that a leaf node will have rgt equal to lft + 1, and you're set. _{pardon the pun.}

Answer 2

In order to specify and differentiate leaf nodes, keep them with left=right. This changes two things:

1. Leaves are easily identifiable.
2. When doing an insertion, you will add only one to the values (left where > new leaf, right where >= leaf).