0

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

Jasmine
  • 5,186
  • 16
  • 62
  • 114
  • What exactly are you attempting to accomplish (perhaps sample data and desired results)? It's unclear from your question why you need to `join` the table to itself. – sgeddes Sep 19 '14 at 01:00
  • Thank you, I want to learn self join to apply for this condition. Donal mentioned below. – Jasmine Sep 19 '14 at 01:33

1 Answers1

2

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

Community
  • 1
  • 1
Donal
  • 31,121
  • 10
  • 63
  • 72
  • Thank you, but how to do with self join? – Jasmine Sep 19 '14 at 01:26
  • That is a self join. The table is being joined to itself - employee is being joined to employee. To do a self join - just use a different alias. In the example I have used e and m as different aliases for the same table - employee. – Donal Sep 19 '14 at 01:29
  • 2
    There is a good explanation of self joins here: http://stackoverflow.com/questions/2458519/explanation-of-self-joins – Donal Sep 19 '14 at 01:34
  • Donal, thank yous o much, here I have only one question. What is "id" and "ManagerId". Are these both different? – Jasmine Sep 19 '14 at 01:36
  • Also, we don't use the word "Self"? SELF JOIN – Jasmine Sep 19 '14 at 01:37
  • id is the unique identifier for the employee table. It uniquely identifies each employee. It is the primary key. ManagerId is the identifier for each manager. – Donal Sep 19 '14 at 01:38
  • 1
    There is no 'SELF JOIN' in SQL. SELF JOIN is a concept. For more info, see here: http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins – Donal Sep 19 '14 at 01:40
  • Thank you for your time, last question if you don't mind, as per what you say, then shouldn't it be e.Name? rather than m.Name – Jasmine Sep 19 '14 at 01:41