Tuesday, 9 July 2013

self join in sql sever

Self Join::

             A self join is a join in which a table is joined with itself,specially when the table has
a foreign key which references its own primary key.
         in other words self join means two instances of the same table will be joined
in the query.

for your better understanding Check below example.

Step 1 ::Create Table and fill some data in that table

   create table #emp_info (empno int,ename varchar(100),manage_id int)

  insert into #emp_info (empno,ename ,manage_id )values(1,'sandip',3)
  insert into #emp_info (empno,ename ,manage_id )values(2,'harshal',1)
  insert into #emp_info (empno,ename ,manage_id )values(3,'aditya',4)
  insert into #emp_info (empno,ename ,manage_id )values(4,'pranav',1)
  insert into #emp_info (empno,ename ,manage_id )values(5,'ajit',3)

Step 2 :: Query for self join

Select e.empno, e.ename, m.ename 'Manager' from #emp_info m, #emp_info e where e.manage_id =m.empno

in above query we are apply join on two instance e and m of #emp_info

Step 3 :: Output

Related Posts ::

1)               Joins in Sql server
2)               Interview queries in sql server
3)               SET ANSI_NULLS in sql server
4)               SET QUOTED_IDENTIFIER in sql
5)               Dynamic Query in sql server
6)               Basic About View
7)               send sql table data via email using store procedure

No comments:

Post a Comment