self join table to get hierarchy for specific root node-CodePudding

Im using postgresql db and i want to use sql get the children of the root node '0' of the table below. Most examples online show how to get all the hierarchies (thats easy with self-join).

However i want to inform the root (say '0') and then only get that hierarchy. My table below has 2 hierarchies (root entries '0' and 'a').

Data

Id	ParentId
0	null
1	0
2	0
3	1
a	null
b	a

Expected result

Id	ParentId
0	null
1	0
2	0
3	1

CodePudding user response：

You want to use a recursive CTE to do to this. They are complicated to wrap your head around, but they are very common solution for a parent/child hierarchy table.

In your case it would look something like:

WITH recCTE AS 
(
   /* Here we select the starting point for the recursion */
   SELECT Id, ParentId FROM yourtable WHERE Id = 0
   UNION ALL
   /* Here we select how the recursive result set joins to 
    * to the original table
    * 
    * This will run over and over again until the join fails
    * appending records to the recCTE result set as it goes.
    */
   SELECT 
      yourtable.Id,
      yourtable.ParentId
   FROM recCTE
      INNER JOIN yourtable 
          ON recCTE.Id = yourtable.ParentId
)
SELECT * FROM recCTE;

CodePudding user response：

A basic recursive CTE will produce the result you want. For example:

with recursive
n as (
  select * from t where id = 0
 union all
  select t.* from n join t on t.parentid = n.id
)
select * from n