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,
We 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.

with EmployeesWithDesi as
(
  select employee.EmployeID,employee.Name,designation.Name as DesignaitonName,employee.ReportingTo
  from [dbo].[tbl_Employee] as employee
  left join [dbo].[tbl_Designation] as designation
  on designation.DesignationID= employee.DesgiantionID

)
select ED2.EmployeID,ED2.Name as Employee,ED2.DesignaitonName as Designation,ED1.Name as ReportingBoss,ED1.DesignaitonName as ReportingBossDesignaion  from EmployeesWithDesi as ED1
join EmployeesWithDesi as ED2
on ED1.EmployeID=ED2.ReportingTo

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

Fig: Query Result.

We have done everything! Happy Coding.