Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

self join query in sql server | self join example in sql server

Jul 5, 2012
Introduction:

Here I will explain how to write self join query in SQL server and self join example in SQL server.

Description:

In previous article I explained about Joins in SQL Server and many more articles relating to SQL Server. Now I will explain about self join in SQL Server.

Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

Syntax for self join


SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;
Here I will explain self join with one example for that first design one table and give name as EmployeeDetails in your database as shown below

Column Name
Data Type
Allow Nulls
EmpId
Int (set Identity=true)
No
EmpName
varchar(50)
Yes
ManagerId
Int
Yes
Once table designed please enter the data in your table that as shown below.

EmpId
EmpName
ManagerId
     1
Suresh
0
     2
Prasanthi
1
     3
Mahesh
1
     4
Sai
2
     5
Madhav
2
     6
Honey
5
Now if I want get the details of Empolyees who are in Manager Position for that we need to write query like this


SELECT DISTINCT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;
If I run above query we will get records like this

EmpId
EmpName
     1
Suresh
     2
Prasanthi
     5
Madhav
Suppose if I want get the details of Empolyees who are having Managers then we need to write query like


SELECT e2.EmpId, e2.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;
If I run above query we will get records like this

EmpId
EmpName
     2
Prasanthi
     3
Mahesh
     4
Sai
     5
Madhav
     6
Honey

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

8 comments :

Anonymous said...

i think your query are wrong you repeat same query for same result

SELECT e2.EmpId, e2.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

also you must specifiy join like
i think for first one this going to be write

select distinct e1.EmpId as 'EMPLOYEE ID', e1.EmpName as 'MANAGER NAME' FROM EmployeeDetails e1 inner join
EmployeeDetails e2 on e2.ManagerId=e1.EmpId;

Suresh Dasari said...

the query whatever i written it's correct only check it once and the query you written will return same values check it once.

Anonymous said...

Sir please explain 2nd query

SaiPrasad said...

Can u Post a real Time Example on C# Generics?

Shashikant said...

hello Sir,

I ran your first query but results were little different.

It returned :

Suresh
Suresh
Prashant
Prashant
Mahadev

Could you please explain why i am getting this result on running first query:
Here I used em1, em2 instead of e1 and e2.

select em1.EmpName from EmployeeDetails em1 , EmployeeDetails em2 where em1.EmpID=em2.ManagerId

Suresh Dasari said...

@shashikant...
Actually here suresh is manager for two employees and prasanthi is manager for other two employees that's why you are getting two times. please use DISTINCT in your query like as shown above.

Anonymous said...

Hi Suresh,
Good eg !!!

vivek said...


SELECT DISTINCT e2.Id, e1.Name as Emp,e2.Name as Mgr FROM #tmptbl1 e1, #tmptbl1 e2
where e1.Id=e2.MId;

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.