In this Article we will learn about Hierarchical database model with real example and SQL query.
You can always download the source code here.
First we know,
What is Hierarchical database model:
Hierarchical database model is organized data in database like tree. The data is stored as record which are connected one another thought links. The structure is based on the rule one parent can have many children but children is not allow many parent. Single children can have one parent .
Bellow is the Example of a hierarchical model
We see above picture CEO have three children COO,CTO and CIO. COO and CIO have not any children. CTO have tow children . We will approve this in our query, After few time. For this region we will use tow table one is named “tbl_Designation” and other is named “tbl_Employee”. Those table have gived bellow.
First Once :
Another Once :
We will do this simple query. this query is nothing just selfjoin in table Bellow 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
After execute above query.we can see bellow result.
Fig: Query result.
We have done everything! That’s fantastic, right? Happy coding.