Saturday, March 29, 2008

How To Use Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id

emp_name

emp_manager_id

1

John

Null

2

Tom

1

3

Smith

1

4

Albert

2

5

David

2

6

Murphy

5

7

Petra

5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:

manager

employee

John

Tom

John

Smith

Tom

Albert

Tom

David

David

Murphy

David

Petra


Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

39 comments:

Dinesh said...

VERY USEFUL ARTICLE. THANKS MAN

abhishek said...

what a way of teaching.......
i m too happy, thank u very much.

Pharees said...

how would you go about including the null value in the query? i.e. if you wanted to include the fact that employee john has no manager

Deep said...

Thank you so much.
Good explaination.

Deepak patil

Vasu Babu said...

well... thank u ....
so use full for learner....

ruler4all said...

hi..thanks im feeling this as spoonfeeding

rohit said...

Hey dear,nice post..but if i want to populate data based on one condition like i want to display employees who's manager is Me and employees whose manager are those who are my employees..n similar to it..Thanks ..
kindly reply soon...
I need it urgent

tinker said...

thanx for posting such a useful info.

Reshma said...

nice article :)

SUresh Kumar Miryala said...

Hi Rohit,

Your scenario may be invalid since a manager cannot be a self reporting manager but even then it filters out if you use the same query

varun said...
This comment has been removed by the author.
Vinay Singh said...

Hi, I have a 'Emp' table like
Empid Fname Lname Mgrid
1 x Last x
2 First x 1
Want to join and the result should be like "First Last" in one row

Written a query like,
"Select A.Fname, B.Lname
From Emp A join Emp B
on A.Mgrid = B.Empid"

but getting the result with duplicate records 3-4 times like
First Last
First Last
First
First Last
First Last

Vinay Singh said...

How can i remove the duplicates on my above problem? 'Distinct' still keep result with two rows like
First Last
First

Vinay Singh said...

Hi, I have a 'Emp' table like
Empid Fname Lname Mgrid
1 x Last x
2 First x 1
Want to join and the result should be like "First Last" in one row

Tried with the query like,
"Select Distinct A.Fname, B.Lname
From Emp A join Emp B
on A.Mgrid = B.Empid"
or
"Select Distinct A.Fname, B.Lname
From Emp A, Emp B
where A.Mgrid = B.Empid"
or
"Select Distinct A.Fname, B.Lname
From Emp A
Left join Emp B on A.Mgrid = B.Empid"

but getting the results like,
First Last
First

Can any one help in removing the second row from the results?

nandan said...

very nice artical thaks a lot

gunnas said...

Useful article to learn self join clearly, Thanks dear .............

by
Gunnas

R.B. Prajapati said...

Thanks you so much dear.this is good explanation.and hope that give other query like it

siddhu said...

Hi lot of thanks for bring the valuable information



BY
Siddu

developer said...

VERY USEFUL ARTICLE. THANKS

developer said...

VERY USEFUL ARTICLE. THANKS

Sundip said...

nice explanation, thanks man

yasin kızılırmak said...

thankss man...

swapna c said...

Nice explanation

kaku said...

can any one please tell me what are the usages of this Self join as i am new to this field..when and where what are its advantages and limitations

adi grandhi said...

hi vinay singh

i solved you are query like as follows

select d.fname+' '+d1.lname from dummy as d join dummy as d1 on d.mgrid=d1.empid

Results like this as,
"FirstLast"

Ramesh said...

Good job continue

Ramu alagappan said...

Thanks for giving such as useful article. Thanks a lot.

papia said...

thanks....i have to take class of 3rd yr students tomorrow on this ...
thank you vary much....

Ramesh said...
This comment has been removed by the author.
Ramesh said...

Tank u vry vry much 4 ur clr xplanation.....:-):)

Pradeep said...

thank u very much ........
Good artical for understand meaning of self join ...

thanx once again...

lalita singh said...

thnxxxxxxxxxxxx a lot of this

lalita singh said...

u no finished ma self join problem

kalaivendan said...

Very useful article.. Thanks...

yusuf said...

THANK YOU SOOOOOO Much

yjreddy said...

i didnt understand e1 and e2 you have taken, iam a beginner of sql server
can u explain me plz...with an example...

J4H said...

Really its use full for learners, done good job......

e1 and e2 are the objects for the EMP table, we can use same table for the self join na, so we should declare like and use them.

Thanks,
JJ

siddharth sawant said...

really nice article, useful very very useful and understandable

heemanshu bhalla said...

* Sql Introduction
* Using Stored Procedures
* sql queries
* groupby functions etc
* Joins

Complete explanation

Link here for Sql Tutorial
http://geeksprogrammings.blogspot.in/search/label/StructuredQueryLanguage%28SQL%29

Link here for joins explanation
geeksprogrammings.blogspot.in/2013/06/joins-in-sql.html