Click here to Skip to main content
15,067,539 members
Articles / Programming Languages / T-SQL
Posted 18 Aug 2016

Tagged as


11 bookmarked

Hierarchical Query in Database

Rate me:
Please Sign up or sign in to vote.
3.33/5 (12 votes)
27 Aug 2016CPOL
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.


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!


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


About the Author

Shamim Uddin
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
MemberMad 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
MemberMember 1269394619-Aug-16 9:21 
GeneralMy vote of 5 Pin
bdmahfuzbappy19-Aug-16 9:05
Memberbdmahfuzbappy19-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.