Hierarchical data - SQL Get lowest level child and root node

Questions

I have database schema: [Id], [ParrentId], [some more tables]

I have hierarchy like:

1. a
 2. aa
  3. aaa_1
  3. aaa_2
1. b
 2. bb
1. c
 2. cc
  3. ccc_1
   4. cccc
  3. ccc_2

I want a (select * where X) => [X, lowest leve child] like: [a, aaa_1] [a, aaa_2]; [cc, cccc] etc.

I can get lowest child with

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

but I don t know how to join it with root node.

Answers

Given:

• The DBMS is SQL Server;
• The highest level nodes of the tree have parent = NULL;
• You want all the lowest leaves for all levels of the trees, not just the roots;
• You want to have all the nodes at a lowest level, not just one;

This query would do it:

WITH r ( category_id, name, root, depth )
-- finds the root relationship
AS (
    SELECT category_id, name, category_id, 0
        FROM category
        -- WHERE parent IS NULL -- this would only look at root nodes
    UNION ALL
    SELECT c.category_id, c.name, r.root, r.depth + 1
        FROM r
        JOIN category c
            ON c.parent = r.category_id
), s ( category_id, name, root, window_id )
-- finds the lowest leaves
AS (
    SELECT category_id, name, root, RANK() OVER(partition by root order by depth DESC)
        FROM r
)
SELECT c.name AS NodeName, s.Name AS DeepLeafName
    FROM category c
    JOIN s
        ON c.category_id = s.root
        WHERE s.window_id = 1;

Here is the result set:

https://i.stack.imgur.com/eNJlR.png https://i.stack.imgur.com/eNJlR.png

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/34947770/sql-get-lowest-level-child-and-root-node

Related