Hi friend
I want to know what is self join in in sql server?
Thank you.
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Jignesh TrivediPosted May 7, 2012, 11:25 PM
A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
Example
SELECT a.*,b.*
FROM Table1 a, Table1 b
WHERE a.pk = b.pk1;
Please refer
http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/
hope this help.
SenthilkumarPosted May 7, 2012, 11:02 PM
I suggest you to go through this pinal dave article for good understanding with suitable example.
http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/
Satyapriya NayakPosted May 7, 2012, 10:02 PM
When all of the data you want to retrieve is contained within a single table, but data needed to extract is related to each other in the table itself.
In our example, 3 columns emp_id, emp_name and emp_mgr. Now
For example: Table named Emp_details containing information of all employees, Now you want to know the manager for every employee.
Follow as below, to create and insert the data to proceed for the self join example.
create table Emp_details
(
emp_id int,
emp_name varchar(25),
mgr_no int
)
insert into Emp_details
select 1, 'Jeff', 4
union all
select 2, 'Mark', 4
union all
select 3, 'Ben', 4
union all
select 4, 'Kenny', NULL
After insertion, You will see Kenny's emp_id is 4 and her mgr_no is NULL which is 4 for others.
Self join can be an inner or outer join, lets make a self join to retrieve details of employees with their respective manager.
select t1.emp_id, t1.emp_name, t2.emp_name from Emp_details as t1 inner join Emp_details as t2 on t1.mgr_no = t2.emp_id
Please refer the below link
http://hightechpost.blogspot.in/2011/07/self-join-in-sql-server.html
http://learnsqlserver.in/3/Self-Join.aspx
Thanks