Reports

Here's an option by using WITH RECURSIVE in an Items table having ItemID, Name and ParentID:

WITH RECURSIVE ItemsTree AS (
    -- Start with the given parent ItemID
    SELECT ItemID, Name, ParentID
    FROM Items
    WHERE ItemID = 50

    UNION ALL

    -- Recursively find children of each node
    SELECT i.ItemID, i.Name, i.ParentID
    FROM Items i
    INNER JOIN ItemsTree it ON i.ParentID = it.ItemID
)

-- Select all nodes in the tree under the given ItemID
SELECT *
FROM ItemsTree;

This one worked like a charm for me, returning not only the leaves but also all the mid-nodes of my hierarchical tree.

79617211