Click here to Skip to main content
15,867,594 members
Articles / Programming Languages / T-SQL
Tip/Trick

Hierarchical Query in Database

Rate me:
Please Sign up or sign in to vote.
3.33/5 (12 votes)
27 Aug 2016CPOL 13.2K   88   11   4
We will learn about hierarchical query in database with real example.

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.

hi

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:

5657567567

Figure: tbl_Designation

Another one is as follows:

555555555

Figure: tbl_Employee

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

SQL
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:

111111111111111

Figure: Query result

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Amber Software Solution Ltd.
Bangladesh Bangladesh
Hi, I am Shamim Uddin.Working with Microsoft Technologies.

Comments and Discussions

 
General[My vote of 2] This Hierarchy has neither depth nor traversal. Pin
Mad Myche22-Aug-16 9:08
Mad Myche22-Aug-16 9:08 
GeneralRe: [My vote of 2] This Hierarchy has neither depth nor traversal. Pin
Shamim Uddin17-Sep-16 7:59
professionalShamim Uddin17-Sep-16 7:59 
GeneralMy vote of 5 Pin
Member 1269394619-Aug-16 9:21
Member 1269394619-Aug-16 9:21 
GeneralMy vote of 5 Pin
bdmahfuzbappy19-Aug-16 9:05
bdmahfuzbappy19-Aug-16 9:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.