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.