Self Joins in SQL Server-
Find the Manager Name for each employee in the employee table
A
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;
![Image1.jpg]()
SELECT
e.empid, e.name, m.name "Manager" FROM
Emp e, Emp m WHERE
e.mgrid=m.empid;
![Image2.jpg]()