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

mgrid 
int

)

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,e.name,m.name as 'mgr name' from Emp e, Emp m where e.mgrid =m.empid
select e.empid,e.name,m.name as 'mgr name' from Emp e, Emp m where m.empid = e.mgrid



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


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





Thanks
~ 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

    ReplyDelete

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (9) Web Service (1) XMl (1)