oracle - SQL Tree Structure -

i new topic (i.e tree structure) in sql. have gone through different sources still not clear.

here in case, have table have attached herewith.

now here first, have retrieve full tree “office”.

also have find leaf nodes (those no children) in attached hierarchical data.

please provide answers detail explanation. in advance.

you didn't specify dbms standard sql (supported modern dbms) can recursive query full tree:

with recursive full_tree (   select id, name, parent, 1 level   departments   parent null   union    select c.id, c.name, c.parent, p.level + 1   departments c     join full_tree p on c.parent = p.id ) select * full_tree; 

if need sub-tree, change starting condition in common table expression. e.g. "categories":

with recursive all_categories (   select id, name, parent, 1 level   departments   id = 2 --- << start different node    union    select c.id, c.name, c.parent, p.level + 1   departments c     join all_categories p on c.parent = p.id ) select * all_categories; 

getting leafs straightforward: it's nodes id not appear parent:

select * departments id not in (select parent                  departments                  parent not null); 

sqlfiddle example: http://sqlfiddle.com/#!15/414c9/1

---

edit after dbms has been specified.

oracle support recursive ctes (although need 11.2.x that) need leave out keyword recursive. can use connect by operator:

select id, name, parent, level departments start parent null connect prior id = parent;  select id, name, parent, level departments start id = 2 connect prior id = parent; 

sqlfiddle oracle: http://sqlfiddle.com/#!4/6774ee/3

see manual details: https://docs.oracle.com/database/121/sqlrf/queries003.htm#i2053935