CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Jasmine
Jasmine

Reputation: 5326

Query for a self join for employee table

Suppose I have an employee table. I have Name and Manager columns. Say there are 10 employees of which 2 are managers. So Name will have 10 names and Manager name would be in Manager column.

How to use self join? I am just learning self join

Upvotes: 0

Views: 3482

Answers (1)

Donal
Donal

Reputation: 32713

To perform a self join, you simply give the same table a different alias. For example, in your employee table you would have a managerid - which stores the id of the manager. Then to get the manager's name - you just self join to the employee table on managerid - using a different alias (I have used m in the example below):

For example, your table would look like this:

CREATE TABLE Employees (id INT, Name VARCHAR(20), ManagerId INT);

To get the Employee's Name and his/her Manager's Name, you would do something like this:

SELECT 
e.Name AS EmployeeName,
ISNULL(m.Name, 'No Manager') AS ManagerName
FROM employee e 
LEFT JOIN employee m on m.id = e.ManagerId

If you want to learn more about self joins - see here

Upvotes: 2

Related Questions