Tree Hierarchy In Database

In this blog I posted a stored procedure which finds hierarchical data in a database.

As an example I use employee table. The superssn is the supervisor's ssn number. 

You must define super_empssn and then provide a valid ssn number for super_empssn variable.

-- Total count of first level subordinates of a given employee

SELECT *

FROM employee parent, employee child

WHERE parent.ssn = super_empssn

AND parent.ssn  = child.superssn;

-- Second level subordinates of a given employee

SELECT *

FROM 

(

SELECT child.*

FROM employee parent, employee child

WHERE parent.ssn = super_empssn

AND parent.ssn  = child.superssn

ORDER BY child.ssn) children, employee grandchild

WHERE children.ssn = grandchild.superssn;

-- Third level subordinates of a given employee

SELECT *

FROM

(

SELECT grandchild.*

FROM (

SELECT child.*

FROM employee parent, employee child

WHERE parent.ssn = super_empssn

AND parent.ssn  = child.superssn

ORDER BY child.ssn) children, employee grandchild

WHERE children.ssn = grandchild.superssn

ORDER BY grandchild.ssn) greatchildren, employee greatgrandchild

WHERE greatchildren.ssn = greatgrandchild.superssn;