Postgresql Query for Getting N-Level Parent-Child Relation Stored in a Single Table

Postgresql query for getting n-level parent-child relation stored in a single table

With Postgres you can use a recursive common table expression:

with recursive rel_tree as (
   select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info
   from relations 
   where rel_parent is null
   union all
   select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id
   from relations c
     join rel_tree p on c.rel_parent = p.rel_id
)
select rel_id, rel_name
from rel_tree
order by path_info;

SQLFiddle based on your example: http://sqlfiddle.com/#!11/59319/19

(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)

PostgreSQL finding nearest parent value of a certain level

You can put a stop condition on the first matching partner in the recursion, then filter the result:

WITH RECURSIVE tree AS ( 
   SELECT "ChildDisplayID" as initialid, "ChildDisplayID", "ParentID", "Rank", 1 as level 
   FROM table1
   WHERE "ChildDisplayID" = {{some ChildID}}
   UNION ALL 
   SELECT t.initialid, t1."ChildDisplayID", t1."ParentID", t1."Rank", t.level + 1
   FROM table1 t1
   INNER JOIN tree t ON t."ParentID" = t1."ChildDisplayID"
   WHERE t."Rank" <> 'Partner'
)
SELECT *
FROM tree
WHERE "Rank" = 'Partner'

It seems like you have a hierarchy where each child has just one parent, so there should be only one match, or no match at all.

Need help to construct the query from parent-child structure table

Here is an recursive CTE for your query. I haven't finished the formatting but it gives you the recursive CTE.

You haven't specified your RBMS, this is in SQL server. See dbFiddle link at the bottom.

create table nodes(
Rec int,
Node varchar(5),
Parent_Node varchar(5));

insert into nodes values
(1,'A',null),
(2,'B',null),
(3,'C1','A'),
(4,'EE','A'),
(5,'EF','B'),
(6,'G1','B'),
(7,'HH','C1'),
(8,'IU','C1'),
(9,'JK','EF'),
(10,'JA','G1'),
(11,'JJ','G1'),
(12,'OT','HH'),
(13,'PT','JA');

with noder as 
( 
select  
  Parent_Node n1, 
  Node n2, 
  cast(null as varchar(5)) n3, 
  cast(null as varchar(5)) n4, 
  cast(null as varchar(5)) n5
from nodes
  union all 
select 
  Parent_node,n1,n2,n3,n4
from nodes
join noder on n1 = Node 
)
select *
from Noder 
where n1 is not null

n1 | n2 | n3   | n4   | n5  
:- | :- | :--- | :--- | :---
A  | C1 | null | null | null
A  | EE | null | null | null
B  | EF | null | null | null
B  | G1 | null | null | null
C1 | HH | null | null | null
C1 | IU | null | null | null
EF | JK | null | null | null
G1 | JA | null | null | null
G1 | JJ | null | null | null
HH | OT | null | null | null
JA | PT | null | null | null
G1 | JA | PT   | null | null
B  | G1 | JA   | PT   | null
C1 | HH | OT   | null | null
A  | C1 | HH   | OT   | null
B  | G1 | JJ   | null | null
B  | G1 | JA   | null | null
B  | EF | JK   | null | null
A  | C1 | IU   | null | null
A  | C1 | HH   | null | null

db<>fiddle here

Get access from tables with parent child relationship

The problem here is that you start your hierarchy with nodes having b as a parent. These are nodes c and d. Node c has access 2 explicitly assigned, but node d does not. The query does not "see" the access of 3 assigned to node b because that row is not included in the query.

This query is how I would approach the problem. It generates the entire hierarchy and then limits it to rows that have b as the first node in its path.

Fiddle Here

with recursive descendants as ( 
  select h.parent, h.node as descendant, 1 as level, ad.access,
         array[h.node]::text[] as path
    from hierarchy h
         left join accessdetails ad on ad.node = h.node
  union all
    select d.parent, s.node, d.level + 1, 
           coalesce(ad.access, d.access), d.path||s.node::text
      from descendants as d
           join hierarchy s
             on d.descendant = s.parent
           left join accessdetails ad on ad.node = s.node
  ) 
select descendant, level, access, path
  from descendants
 where path[1] = 'b'
 order by parent, level, descendant ;

postgres - with recursive

First of all, your (2, 'grandparent', null) should be (3, 'grandparent', null) if it really is a grandparent. Secondly, your (implicit) join condition in the recursive half of your query is backwards, you want to get the parent out of rt.levelparent rather than t.parent_level:

WITH RECURSIVE recursetree(level_id, levelparent) AS (
    SELECT level_id, parent_level 
    FROM level 
    WHERE level_id = 197

    UNION ALL

    SELECT t.level_id, t.parent_level
    FROM level t JOIN recursetree rt ON rt.levelparent = t.level_id
    -- join condition fixed and ANSI-ified above
)
SELECT * FROM recursetree;

Parent/child table with include/exclude items

demo:db<>fiddle

WITH RECURSIVE items AS (
    SELECT                -- 1
        group_id,
        array_agg(name)
    FROM 
        group_Item
    GROUP BY group_id

    UNION

    SELECT DISTINCT 
        parent_id, 
        array_agg(unnest) FILTER (WHERE bool_and) OVER (PARTITION BY parent_id) -- 5
    FROM (
        SELECT 
           parent_id,
           unnest,
           bool_and(contains) OVER (PARTITION BY parent_id, unnest) -- 4
        FROM items i 
        JOIN group_children gc           -- 2
        ON i.group_id = gc.children_id,
        unnest(array_agg)                -- 3
    ) s
)
SELECT * FROM items

1. The non-recursive part aggregates all names per group_id
2. Recursive part: Joining the chilren against their parents
3. Expanding the name arrays into one element per row.

This results in:

| group_id | array_agg | id | parent_id | children_id | contains | unnest |
|----------|-----------|----|-----------|-------------|----------|--------|
|        4 | {aaa,bbb} |  3 |         2 |           4 | true     | aaa    |
|        4 | {aaa,bbb} |  3 |         2 |           4 | true     | bbb    |
|        5 | {bbb,ccc} |  4 |         2 |           5 | false    | bbb    |
|        5 | {bbb,ccc} |  4 |         2 |           5 | false    | ccc    |
|        6 | {aaa,bbb} |  5 |         3 |           6 | true     | aaa    |
|        6 | {aaa,bbb} |  5 |         3 |           6 | true     | bbb    |
|        7 | {aaa,ccc} |  6 |         3 |           7 | false    | aaa    |
|        7 | {aaa,ccc} |  6 |         3 |           7 | false    | ccc    |

4. Now you have the unnested names. Now you want to find the ones that have to be excluded. Taking the bbb element for the parent_id = 2: There is one row with contains = true and one with contains = false. This should be excluded. Therefore all the names per parent_id have to be grouped. The contains values can be aggregated with boolean operators. The aggregate function bool_and gives only true if all elements are true. So bbb would get a false (The aggregation needs to be done as a window function because GROUP BY is not allowed within the recursive part for some reasons):

Result:

| parent_id | unnest | bool_and |
|-----------|--------|----------|
|         2 | aaa    | true     |
|         2 | bbb    | false    |
|         2 | bbb    | false    |
|         2 | ccc    | false    |
|         3 | aaa    | false    |
|         3 | aaa    | false    |
|         3 | bbb    | true     |
|         3 | ccc    | false    |

5. After that the unnested names can be grouped per parent_id. The FILTER clause only aggregates the elements where the bool_and is true. Of course you need to do this in a window function again. This creates duplicate records which can be removed by the DISTINCT clause

Final result (which of course could be filtered by the element 1):

| group_id | array_agg |
|----------|-----------|
|        5 | {bbb,ccc} |
|        4 | {aaa,bbb} |
|        6 | {aaa,bbb} |
|        7 | {aaa,ccc} |
|        2 | {aaa}     |
|        3 | {bbb}     |
|        1 | {aaa}     |

PostgreSQL recursive parent/child query

To get all children for subject 1, you can use

WITH RECURSIVE c AS (
   SELECT 1 AS id
   UNION ALL
   SELECT sa.child_id
   FROM subject_associations AS sa
      JOIN c ON c.id = sa. parent_id
)
SELECT id FROM c;

---

Related Topics