Saturday, 18 October 2014

Self Joins 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),



Insert into Emp(empid,name,mgrid) values (1001,'Manish Agrahari',1001); 

Insert into Emp(empid,name,mgrid) values (1002,'Deepti',1001);
Insert into Emp(empid,name,mgrid) values (1003,'Amit',1001);
Insert into Emp(empid,name,mgrid) values (1004,'Sandy',1002);
Insert into Emp(empid,name,mgrid) values (1005,'Ankit',1003);
Insert into Emp(empid,name,mgrid) values (1006,'Kapil',1002);
Select * from Emp;

select e.empid,, as 'mgr name' from Emp e, Emp m where e.mgrid =m.empid
select e.empid,, as 'mgr name' from Emp e, Emp m where m.empid = e.mgrid

Fetch the Managers
SELECT DISTINCT e1.EmpId, FROM Emp e1, Emp e2 where e1.EmpId=e2.mgrid; 

Fetch the Employee who has Managers
SELECT DISTINCT e2.EmpId, FROM Emp e1, Emp e2 where e1.EmpId=e2.mgrid; 

~ Suraj K.

1 comment:

  1. Pretty blog, so many ideas in a single site, thanks for the informative article, keep updating more article.
    Webdesining course in chennai



ADFS (1) ADO .Net (2) Ajax (1) Angular (1) Angular Js (17) Angular2 (28) ASP .Net (14) Authentication (1) Azure (1) Breeze.js (1) C# (50) CD (1) CI (2) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DevOps (4) DI (4) Dotnet (22) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (8) Lamda (3) Linq (11) Mongodb (1) MVC (50) NodeJS (7) RDLC (1) Report (1) SDLC (1) Sql Server (30) SSIS (3) SSO (1) SSRS (2) UI (1) WCF (13) Web Api (11) Web Service (1) XMl (1)