Useful queries (self join by example)

How to find out the Manager of respective employee from a single table (Self Join by example)

Suppose here is a table called employee:-

empId       name           mngrId 
   1            Alok                 2
   2            Ashu                 3
   3            Manu                4
   4            Ravi                  5
   5            Baby                 1

Now the requirement is to find out the manager of each employee. Lets do it step by step:-

Step 1- Create a table with following statement:


CREATE TABLE employee(
empid INT
,name VARCHAR(20)
,mngrId INT
)
GO

Step 2- Insert the data into employee table:


INSERT INTO employee SELECT 1, 'Alok', 2
INSERT INTO employee SELECT 2, 'Ashu', 3
INSERT INTO employee SELECT 3, 'Manu', 4
INSERT INTO employee SELECT 4, 'Ravi', 5
INSERT INTO employee SELECT 5, 'Baby', 1
GO

Step 3- Write the query to fetch the required result:


SELECT e1.name empName, e2.name mngrName
FROM employee e1
      JOIN employee e2 ON e1.mngrId = e2.empid
GO

Thanks.