Thursday, 30 November 2017

Self join in sql server




Self Joins in SQL Server- Find the Manager Name for each employee in the employee table
self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.
For example the following query returns employee names and their manager names for whom they are working.
Create table Emp
(
empid int primary key,name varchar(50),mgrid int)
Insert into Emp(empid,name,mgrid)values (1001,'Anand Upadhyay',1001); 





Insert into Emp(empid,name,mgrid)values (1002,'Shiv',1001);
Insert into Emp(empid,name,mgrid)values (1003,'Pawan Bhati',1001);
Insert into Emp(empid,name,mgrid)values (1004,'Pankaj Sharma',1002);
Insert into Emp(empid,name,mgrid)values (1005,'Harish Yadav',1003);
Insert into Emp(empid,name,mgrid)values (1006,'Yatesh Chauhan',1002);
Insert into Emp(empid,name)values (1007,'Chitranjan Upadhyay');

Insert into Emp(empid,name)values (1008,'Munish Upadhyay');

Self join query 


SELECT e.empid, e.name, m.name  "Manager" FROM Emp e, Emp m WHERE e.mgrid=m.empid;






1 comment: