Postgresql Recursive query not providing result as expected

Question

I have PostgreSQL table which has records stored as tree values

create table doitestchk(node_id,entity_id,letter,parent_id)as values
 (1,'Entity_id_1','10',null)
,(2,'Entity_id_1','93/rheumatology',1)
,(3,'Entity_id_1','1093',1)
,(4,'Entity_id_1','rheumatology',3)
,(5,'Entity_id_1','journals.rheumatology.a0314',3)
,(6,'Entity_id_1','2015002908',3)
,(7,'Entity_id_1','icc',3)
,(8,'Entity_id_1','8',5)
,(9,'Entity_id_1','9',5);

I want to get all parent letter when queried from child example :

WITH RECURSIVE generation AS (
    SELECT node_id,
           entity_id,
           letter,
           parent_id,
           0 AS generation_number
    FROM doitestchk
    WHERE parent_id IS NULL
    UNION ALL
    SELECT child.node_id,
           child.entity_id,
           child.letter,
           child.parent_id,
           generation_number + 1 AS generation_number
    FROM doitestchk child
    JOIN generation g
      ON g.node_id = child.parent_id
) 
SELECT entity_id,
       letter,
       generation_number
FROM generation 
WHERE letter like '%a0314%';

I am getting only node_id : 5 Instead I need all parent of node_id : 5 which is node_id 3 which is child of node_id :1

Is there any way I can get all parent of the child nodes?

Salt · Accepted Answer · 2024-12-10 14:59:24Z

1

Your query traverses the tree from top to bottom (parent -> child), but you need to find all parent nodes when searching from a specific child node.

This query will return all parent nodes in the hierarchy, starting from your target node and moving upwards through the tree structure:

WITH RECURSIVE parent_hierarchy AS (
    -- Base: start from target node
    SELECT 
        node_id, entity_id, letter, parent_id, 1 as level
    FROM doitestchk
    WHERE letter LIKE '%a0314%'
    
    UNION ALL
    
    -- Recursive: find parents
    SELECT 
        p.node_id, p.entity_id, p.letter, p.parent_id, ph.level + 1
    FROM doitestchk p
    JOIN parent_hierarchy ph ON p.node_id = ph.parent_id
)
SELECT *
FROM parent_hierarchy
ORDER BY level DESC;

answered Dec 10, 2024 at 14:59

Salt

9803 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Recursive query not providing result as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related