How to use recursive CTE with LOOKUP?

user_15659121 0
Viewed 49

I have a very specific use-case for which I'm not even sure if I can use SQL operators.

I have a users table, a branches table, and a users_to_branches view. users_to_branches view exists, because I need id, username, and branch_id from users, and a branch_name (not present in users) and a parentBranch_id from branches table.

Now the organizational chart can be structured in a couple of layers, so as of now branch_id of a user, is a direct branch to which the user is assigned (ex. Finance), but the parentBranch_id is a level above that (ex. Company X). But for example, if there's one more layer, under Finance (ex. Bookkeeping), parentBranch_id would be Finance's branch_id. What I need, is to see the highest level of the structure as the parentBranch_id at all times. So in the case of Bookkeeping as branch_id I want to see Company X as parentBranch_id.

What kind of logic am I looking at? I've read it will have to involve recursive CTE, but how to implement a LOOKUP function and pass the value with it?

My thinking: The function has to constantly check the parentBranch_id. IF NULL, then pass the branch_id as parentBranch_id. IF NOT NULL then look up the parentBranch_id value in the branch_id column - until it's NULL.

mariadb mysql sql
1 Answers

You would get the ultimate parent branch for each branch id using a recursive CTE. Something like this:

with recursive cte as (
      select b.branch_id as ultimate_parent_branch_id, b.branch_id as branch_id
      from branches b
      where parent_branch_id is null
      union all
      select cte.ultimate_parent_branch_id, b.branch_id
      from cte join
           branches b
           on b.parent_branch_id = cte.branch_id           
     )
select *
from cte;

Then you would just use this by joining back to users:

with recursive cte as (
      select b.branch_id as ultimate_parent_branch_id, b.branch_id as branch_id
      from branches b
      where parent_branch_id is null
      union all
      select cte.ultimate_parent_branch_id, b.branch_id
      from cte join
           branches b
           on b.parent_branch_id = cte.branch_id           
     )
select *
from users u join
     user_branches ub
     on u.user_id = ub.user_id join
     cte
     on cte.branch_id = ub.branch_id;
user_11440350
Related