How to fetch all related parent and child records in a table?-CodePudding

What I want to query is with given id, get all related child and also parent records.

Here is an example data set;

id    |    name    |    parent     |
 1          ab            null
 21         aa             1
 33         dd             21   
 55         ee            null
 66         bb             55
 77         cc             33

For id = 33, I want to get;

id    |    name    |    parent     |
 1          ab            null
 21         aa             1
 33         dd             21   
 77         cc             33

And for id = 21 also will return the same records since they have common parent and child records. I tried to use inner join but I don't know how to set id condition.

SELECT t.* FROM table t INNER JOIN table tl ON t.id = tl.parent

CodePudding user response：

Here is one way to do this. You need two hierarchical queries one which start with 33 and traverses up till parent is null and another one which start with 33 and moves down till the leaf.

with recursive cte
  as (select id,name,parent,cast(id as varchar(50)) as concat_val,id as root,cast(1 as int)  as lvl
        from t
       where id=33
       union all
       select a.id,a.name,a.parent,cast(concat(a.id,'/',b.concat_val) as varchar(50)),b.root,cast(b.lvl 1 as int)
         from t a
         join cte b
           on b.parent=a.id
        )
  ,cte2
  as (select id,name,parent,cast(id as varchar(50)) as concat_val,id as root,cast(1 as int)  as lvl
        from t
       where id=33
       union all
       select a.id,a.name,a.parent,cast(concat(a.id,'/',b.concat_val) as varchar(50)),b.root,cast(b.lvl-1 as int)
         from t a
         join cte2 b
           on b.id=a.parent
        ) 
select distinct * from (        
select * 
  from cte
union all  
select * 
  from cte2
)x
order by lvl desc

 ---- ------ -------- ------------ ------ ----- 
| id | name | parent | concat_val | root | lvl |
 ---- ------ -------- ------------ ------ ----- 
|  1 | ab   | null   | 1/21/33    |   33 |   3 |
| 21 | aa   | 1      | 21/33      |   33 |   2 |
| 33 | dd   | 21     | 33         |   33 |   1 |
| 77 | cc   | 33     | 77/33      |   33 |   0 |
 ---- ------ -------- ------------ ------ -----

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=963f0522a3dd3d3d9f945e56ce746203

CodePudding user response：

Your should do like this

select 
    t.id,
    t.name,
    t.parent,
    tl.id,
    tl.name,
    tl.parent,   
from
    table t left join table tl 
    on t.id = tl.parent
where
   t.id = 33

If you know how many desendance, let's say 3, you can do

select
    p1.id,
    p1.name,
    p1.parent,
    p2.id,
    p2.name,
    p2.parent,        
    p3.id,
    p3.name,
    p3.parent,
from
    data as p1 left join data as p2 
      on (p1.id = p2.parent) 
    left join data as p3
      on (p2.id = p3.parent)
where 
    p1.id = 33

A good documentation here https://learnsql.com/blog/do-it-in-sql-recursive-tree-traversal/

In many cases the Nested Set model is preferable to the tree structure. see also https://en.wikipedia.org/wiki/Nested_set_model