How to get name and manager name

Question

+----+-----------+---------+
| id | name      | manager |
+----+-----------+---------+
|  1 | bhupendra |       0 |
|  2 | shyam     |       1 |
|  3 | ram       |       1 |
|  4 | geeta     |       2 |
|  5 | sita      |       1 |
+----+-----------+---------+

i need result like

+-----------+-----------+
| name      | manager   |
+-----------+-----------+
| bhupendra |           |
| shyam     | bhupendra |
| ram       | bhupendra |
| geeta     | shyam     |
| sita      | bhupendra |
+-----------+-----------+

what does your manager table look like? Or... does the manager ID relate to a row in that table? — treyBake, Dec 28 '18 at 12:11
Based on the example, the manager ID is just another row in that table. — Michael, Dec 28 '18 at 12:13
+----+-----------+---------+ | id | name | manager | +----+-----------+---------+ | 1 | bhupendra | 0 | | 2 | shyam | 1 | | 3 | ram | 1 | | 4 | geeta | 2 | | 5 | sita | 1 | +----+-----------+---------+ — Bhupendra Dudhwal, Dec 28 '18 at 12:14
Duplicate https://stackoverflow.com/questions/52235524/sql-query-to-get-the-employee-name-and-their-manager-name-from-the-same-table/52235876#52235876 — Manish Singh, Dec 28 '18 at 12:37

score 1 · Answer 1 · answered Dec 28 '18 at 12:11

1

You could self-join the id column on the manager column:

SELECT    e.name AS name, m.name AS manager
FROM      employee e
LEFT JOIN employee m ON m.id = e.manager

answered Dec 28 '18 at 12:11

Mureinik

297,002
52
306
350

this is valid for only 2 level – Derviş Kayımbaşıoğlu Dec 28 '18 at 12:12
@Simonare that's the output the OP wants - see the expected output in the question. – Mureinik Dec 28 '18 at 12:12
i have only one table – Bhupendra Dudhwal Dec 28 '18 at 12:13
@BhupendraDudhwal This si a self-join - the `from` clause and the `left join` clause use the same table. – Mureinik Dec 28 '18 at 12:14
@BhupendraDudhwal care to share it? – Mureinik Dec 28 '18 at 12:16

score 0 · Answer 2 · answered Dec 28 '18 at 12:13

Check this out

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             manager
  from       employee
  union all
  select     p.id,
             p.name,
             p.manager
  from       employee e
  inner join cte
          on p.manager = cte.id
)
select * from cte;

score 0 · Accepted Answer · answered Dec 28 '18 at 12:13

0

You should use the same table twice using alias

select a.name, b.name as manager 
from my_table a 
left join  my_table b on a.manager  = b.id

answered Dec 28 '18 at 12:13

ScaisEdge

131,976
10
91
107

How to get name and manager name

3 Answers3

Linked

Related