SQL query to get the list of supervisor hierarchy. employee --> supervisor --> supervisor

Question

I have two tables Employee and Department this image shows the manager of every employee. I want to write a SQL query that gives me a list of all the supervisor (Manager, Manager of Manager..).

I just want a single column that displays a list of supervisor when given a particular employee.

E.g. If I give employee id = 202 then I should receive 200,130

 |supervisor |   
 +-----------+
 |   200     |      
 |   130     |

I have this query

WITH emp_dept as(
SELECT employee_id,manager_id 
FROM employee,department
WHERE employee.dept_id= department.dept_id
   ) 

 WITH recursive p as (
    select e1.employee_id, e1.manager_id
    from   emp_dept e1
    where    employee_id = 202

    union all

   select e2.employee_id , e2.manager_id
   from   p
   join   emp_dept e2 ON e2.employee_id = p.manager_id

)
select manager_id
from   p

`

I am not able to use it. I am using pgadmin4.

If anyone could help me with this query I would greatly appreciate it

Possible duplicate of [How to get the employees with their managers](https://stackoverflow.com/questions/7451761/how-to-get-the-employees-with-their-managers) — Rajat Mishra, Apr 22 '18 at 03:16
Thanks for the help but the question you are mentioning does not help me get the list of supervisors. It just deals with the president's supervisor being blank and the query mentioned inside would just give me the list of all the employee and manager (just one). — param trivedi, Apr 22 '18 at 03:47

score 0 · Answer 1 · answered Apr 22 '18 at 04:12

0

I think you can use "hierarchical queries" for Oracle and try this:

select manager_id supervisor 
from employee
start with employee_id = 202
connect by nocycle employee_id = prior manager_id;

answered Apr 22 '18 at 04:12

hekko

292
1
2
6

I am actually using the PostgresSQL pgadmin 4 database and this syntax is not valid. If you can help me with pgadmin syntax it would be really helpful. And thanks for helping me out . – param trivedi Apr 22 '18 at 04:23
@paramtrivedi: you shouldn't accept answers that don't work. If you found the solution yourself, it's perfectly OK to accept your own answer. – Jul 13 '18 at 07:21

score 0 · Accepted Answer · answered Apr 26 '18 at 17:08

This is the solution to my question

  with recursive p as (
WITH emp_dept as(
SELECT employee_id,manager_id 
FROM employee,department
WHERE employee.dept_id= department.dept_id
) 

select e1.employee_id, e1.manager_id
from   emp_dept e1
where  e1.employee_id = 202

union 

select e2.employee_id , e2.manager_id
from   p
join   emp_dept e2 ON e2.employee_id = p.manager_id

)
select manager_id
  from   p

SQL query to get the list of supervisor hierarchy. employee --> supervisor --> supervisor

2 Answers2

Linked

Related