Recursive SQL query for finding matches

I have 5 SQL Tables with the following columns:

tbl_department:

department_id, parent_id


tbl_employee

employee_id, department_id


tbl_department_manager

department_id, employee_manager_id


tbl_request_regular_employee

request_id, employee_id


tbl_request_special_employee

request_id, 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)

We cannot use ORM here since app’s responsiveness is our priority and the script might be called a lot.

Here’s the logic I want to implement:

1. First we query to tbl_department_manager based on employee_id to check whether the current employee is a manager or not (also the employee can be a manager in a few departments). If so, we get a list of department_id (if nothing is found, just return false)
2. If we got at least one id in tbl_department_manager we query to tbl_request_regular_employee AND tbl_request_special_employee based on request_id and get employee_id from both tables (they are the same)
3. Based on employee_id collected above we query to tbl_employee to get a unique list of department_id that the employee belongs to.
4. Finally have a list of unique department_id from p.3 which we can compare to the one (ones) that we got in p.1.
5. The catch is, however, that in tbl_department there might be departments which inherit from the one (ones) that we got from p.1 (so we might need to find it recursively based on parent_id until we find at least one match with one element from p.1). If there’s at least one match between one element in p.1 and one element in p.3 return true. So there’s a need to look for it recursively.

Could someone give a clue how to implement it in MSSQL? Any help would be greatly appreciated.

Answer

declare @employee_id int, @request_id int;

with reqEmployees as (
    select regular_employee_id as employee_id
    from tbl_request_regular_employee
    where request_id = @request_id
  union all      --concatenate the two tables
    select special_employee_id
    from tbl_request_special_employee
    where request_id = @request_id
),
cte as (
    select e.department_id, null as parent_id
    from reqEmployees r
    join tbl_employee e  on e.employee_id = r.employee_id -- get these employees' departments
    union all
    select d.department_id, d.parent_id
    from cte              -- recurse the cte
    join tbl_department d on d.department_id = cte.parent_id   -- and get parent departments
)
-- we only want to know if there is any manager row, so exists is enough
select case when exists (select 1
    from cte   --join on managers
    join tbl_department_manager dm on dm.department_id = cte.department_id
    where dm.employee_manager_id = @employee_id)
  then 1 else 0 end;