Hierarchical Database Model In SQL

What is Hierarchical Database Model?

Hierarchical database model is organized data in a database, like a tree. The data is stored as records which are connected to one another through links. The structure is based on the rule that one parent can have many children but a child is not allowed to have many parents. Single children can have one parent only.

Bellow is the example of a hierarchical model,
hiWe see in the above picture that the CEO has three children COO, CTO and CIO. COO and CIO don't have any children. CTO has two children. We will approve this in our query, soon. For this region, we will use two tables. One is named "tbl_Designation" and the other is named "tbl_Employee". Those tables are shown below.

First Once


Fig: tbl_Designation.


Fig: tbl_Employee

We will use this simple query which is nothing but Self Join in the table, as shown in the following code.

  1. with EmployeesWithDesi as   
  2. (  
  3.   select employee.EmployeID,employee.Name,designation.Name as DesignaitonName,employee.ReportingTo   
  4.   from [dbo].[tbl_Employee] as employee  
  5.   left join [dbo].[tbl_Designation] as designation   
  6.   on designation.DesignationID= employee.DesgiantionID  
  8. )  
  9. select ED2.EmployeID,ED2.Name as Employee,ED2.DesignaitonName as Designation,ED1.Name as ReportingBoss,ED1.DesignaitonName as ReportingBossDesignaion  from EmployeesWithDesi as ED1  
  10. join EmployeesWithDesi as ED2  
  11. on ED1.EmployeID=ED2.ReportingTo  

After executing the above query, we can see the result.


Fig: Query Result.

We have done everything! Happy Coding.