I have a table employee with primary key "Emp_id" and the same primary key's value in the same table's column "Manager_Id", Some employees are the manager of other employees and some are not. Some Employees have manager and some don't have manager, means the column of "Manager_ID" is null.
| Emp_id | Emp_name | Manager_ID |
|---|---|---|
| 1 | John | Null |
| 2 | Ben | 3 |
| 3 | Jolly | 1 |
| 4 | Mike | 1 |
| 5 | Richard | 3 |
Here is the query I'm trying:
select a.Emp_Name as Employee, b.Emp_Name as Manager
from employee a
left outer join employee b on a.Emp_ID and b.Emp_ID
where a.Manager_ID = b.Emp_ID
I have to print Emp_name and Manager Name from only one table, and also where the Manager_ID is "Null" I want a Null value in the resultant table but I am unable to do this.
For example, I need the resultant table like this.
| Employee | Manager |
|---|---|
| John | Null |
| Ben | Jolly |
| Jolly | John |
| Mike | John |
| Richard | jolly |
CodePudding user response：
You can do:
select e.emp_name as employee, m.emp_name as manager
from employee e
left join employee m on m.id = e.manager_id