-1

I have a table like this that parent_id=0 means the current row is the main parent:

---------------------
class_id | parent_id
---------------------
    10        0
    11        10
    12        11
    13        12

I want to get rows whose primary parent class_id equals 10 But when I use the SELECT statement it just returns one row because it has a Hierarchy structure.

SELECT class_id FROM TABLE WHERE parent_id = 10
Hamed
  • 25
  • 7

1 Answers1

1

You can use a typical recursive CTE to walk the graph starting at node 10. For example:

with recursive
n as (
  select * from t where class_id = 10
 union all
  select t.*
  from n
  join t on t.parent_id = n.class_id
)
select * from n
The Impaler
  • 45,731
  • 9
  • 39
  • 76