"Column "parent_id" for "cte" is specified more than once" in SQL recursive query

Question

I have 5 SQL tables with columns as follows:

tbl_request_listEmpB
listEmpB_id request_id

tbl_request_listEmpD
listEmpD_id request_id

tbl_employee
id, parent_id (this one refers to id in tbl_department)

tbl_department
id, parent_id (that one referes to id of parent department)

tbl_department_manager
department_id, manager_employee_id

As input data I have employee_id and request_id.

I need to figure out whether the employee has access to the request (whether he's a manager or not)

Here's the query which is supposed to return 1 if the current user is a manager, 0 otherwise

 with reqEmployees as (
    select listEmpB_id as employee_id
    from tbl_request_listEmpB
    where request_id = ${request_id}
  union all --concatenate the two tables
    select listEmpD_id
    from tbl_request_listEmpD
    where  request_id = ${request_id}
),
cte as (
    select e.parent_id, null as parent_id
    from reqEmployees r
    join tbl_employee e  on e.id = r.employee_id -- get these employees' departments
    union all
    select d.id, d.parent_id
    from cte
    join tbl_department d on d.id = cte.parent_id    -- and get parent departments
)
select case when exists (select 1
    from cte  
    join tbl_department_manager dm on dm.department_id = cte.id
    where dm.manager_employee_id = ${employee_id})
  then 1 else 0 end;

Finally, there's the logic that I believe is implemented in the query above:

1. First we need to identify whether the employee_id is a manager or not. If he is - find in which departments. So we query to tbl_department_manager based on manager_employee_id(=employee_id from input data) to get a list of corresponding department_id and store them in a variable. If the query returned 0 departments - terminate and return false
2. Based on request_id we collect ids of employees from both tbl_request_listEmpB and tbl_request_listEmpD. Later we refer to them as employee_id from reqEmployees
3. Query to tbl_employee based on ids retrieved from p.2 to get parent_id (list of unique departments employees belong to)
4. If there's a match between at least one department from p.1 and a one from p.3 return true
5. If not, there's a need to query to tbl_department and recursively search for a match between at least one element from p.1 and one element in p.3.parent_id

Here's what I mean

Consider the following chart

And here's the corresponding SQL table:

tbl_department (id, parent_id)
dep0   null
dep1   dep0
dep2   dep1
dep3   dep1
dep4   dep2
dep5   dep0

So, if we have a departments list returned from p.1 of ['dep1'] (there might be more than one element, we have to iterate through each element) we need to return true ONLY if from p.3 we've got dep1|dep2|dep3|dep4 - (dep1 descendants including dep1). If ['dep2'] return true if dep2|dep4. So there should at least one match of at least one element from p.1 and recursive result from p.5. I hope I illustrated it in the clearest way possible

Almost forgot - the query above gives me

"Column "parent_id" for "cte" is specified more than once" 

But I don't think that it does what it's supposed to do, I need to rewrite it

Any help would be greatly appreciated

Answer 1

Without some sample data (and parameter values) and expected output for that data (with those parameter values), it's difficult to verify this solution.

I have assumed that your tbl_ou and tbl_department are in fact the same table.

Other than the CTE, it looks like the rest of your code should work. The CTE below now travels "both" directions through the hierarchy (upwards and downwards), finding both parents and children. Note that it only finds parents of parents and children of children, it doesn't find children of parents, for example, so no "siblings", "uncles" or whatever these records should be called!

You may need to cast both fields in the CTE seed record as the relevant data type. Based on the supplied data I have assumed that the datatype for department id (and therefore also for parent_id) is varchar(10).

cte as (
select
    cast(e.parent_id as varchar(10)) as id,
    cast(o.parent_id as varchar(10)) as parent_id,
    0 as iteration  
from
    reqEmployees r
    join tbl_employee e  on e.id = r.employee_id
    join tbl_department o on e.parent_id = o.id 
    --extra table here compared to earlier versions to allow us
    --to traverse hierarchy in both directions

union all

select --This one finds "child" departments
    o.id,
    o.parent_id,
    cte.iteration + 1
from
    cte
    join tbl_department o on o.id = cte.parent_id
where    
    cte.iteration >=0 --prevents siblings/uncles etc

union all

select --This one finds "parent" departments
    o.id,
    o.parent_id,
    cte.iteration - 1
from
    cte
    join tbl_department o on o.parent_id = cte.id 
where    
    cte.iteration <=0 --prevents siblings/uncles etc
  
)

You can test my script using this SQL Fiddle (updated).