Hierarchical Query in Database

• Download source code - 1.3 KB

What is Hierarchical Database Model

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

We see in the above picture that CEO has three children COO, CTO and CIO. COO and CIO do not have any children. CTO has two children. We will approve this in our query after a little time. For this region, we will use two tables, one is named "tbl_Designation" and the other is named "tbl_Employee". Those tables have been given below. First one is as follows:

Another one is as follows:

We will do this simple query. This query is nothing but selfjoin in table. Below is the following query.

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 below result:

We have done everything! That’s fantastic, right? Happy coding!