Question: I am new to this topic (i.e tree structure) in SQL. I have gone through different sources but still not clear.

Here in my case, I have a table which I have attached herewith.

[![enter image description here](https://i.stack.imgur.com/jjhKX.png)](https://i.stack.imgur.com/jjhKX.png)

Now here first, I have to retrieve a Full Tree for “OFFICE”.

Also i have to find all the leaf nodes (those with no Children) in the attached hierarchical data.

Please provide the answers with detail explanation. 
Thanks in advance.

Answer: 
You didn't specify your DBMS but with standard SQL (supported by all [modern DBMS](http://use-the-index-luke.com/de/blog/2015-02/modern-sql)) you can easily do a recursive query to get the full tree:

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

```

If you need a sub-tree, just change the starting condition in the common table expression. To get e.g. all "categories":

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

```

Getting all leafs is straightforward: it's all nodes where their ID does not appear as a `parent`:

```
select *
from departments
where id not in (select parent
                 from departments
                 where parent is not null);

```

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

---

Edit after DBMS has been specified.

Oracle does support recursive CTEs (although you need 11.2.x for that) you just need to leave out the keyword `recursive`. But you can also use the `CONNECT BY` operator:

```
select id, name, parent, level
from departments
start with parent is null
connect by prior id = parent;

select id, name, parent, level
from departments
start with id = 2
connect by prior id = parent;

```

SQLFiddle for Oracle: <http://sqlfiddle.com/#!4/6774ee/3>

See the manual for details: <https://docs.oracle.com/database/121/SQLRF/queries003.htm#i2053935>
There is a pattern design called "hardcoded trees" that can be usefull for this purpose.
[![enter image description here](https://i.stack.imgur.com/EzxnM.png)](https://i.stack.imgur.com/EzxnM.png)

Then you can use this query for finding a parent for each child

```
SELECT level1ID FROM Level2entity as L2 WHERE level2ID = :aLevel2ID

```

Or this one for finding the children for each parent:

```
SELECT level1ID FROM Level2entity as L2 WHERE level2ID = :aLevel2ID

```