CODEWITHSUNDEEP
sqldatabase
user2257017
user2257017

Reputation: 1

Get the output based on column in the same table

I have a table which looks like this: Employee table:

Employee_id  E_Name  Manager_Id 
-------------------------------
1             p          -
2             q          1
3             r          1
4             s          2

Here column Manager_Id denotes the manager's employee Id. Meaning for p there is no manager. for q and r employee with id 1 is manager (p). and for s employee with emp id 2 is manager.

I want to build a query which will return below result:

Employee_id  E_Name  Manager_Name 
-------------------------------
1             p          -
2             q          p
3             r          p
4             s          q

table here has manager name instead of id.

How to achieve above? Thanks in advance

Upvotes: 0

Views: 79

Answers (3)

Jaffy
Jaffy

Reputation: 585

It can easily be done with a LEFT OUTER JOIN:

Try this:

SELECT a.EMPLOYEE_ID, a.E_NAME, b.E_NAME
FROM EMPLOYEE a left outer join EMPLOYEE b
on A.MANAGER_ID = B.EMPLOYEE_ID

Upvotes: 1

TechDo
TechDo

Reputation: 18649

Please try:

select 
    Employee_id, 
    E_Name, 
    (select E_Name from YourTable b where b.Employee_id=a.Manager_Id) as Manager_Name
from YourTable a

Upvotes: 0

Adeel
Adeel

Reputation: 19228

you did not mentioned the database provider, but for MySQL, it is:

SELECT e.employee_id, e.e_name,COALESCE(m.e_name,'-') AS Manager_Name
FROM employee e 
LEFT JOIN employee m ON m.employee_id = e.manager_id

Upvotes: 0

Related Questions