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:
Figure: tbl_Designation
Another one is as follows:
Figure: tbl_Employee
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:
Figure: Query result
We have done everything! That’s fantastic, right? Happy coding!
