title: How to use recursive CTE with LOOKUP?
body: <p>I have a very specific use-case for which I'm not even sure if I can use SQL operators.</p>
  <p>I have a <code>users</code> table, a <code>branches</code> table, and a <code>users_to_branches</code> view. <code>users_to_branches</code> view exists, because I need <code>id</code>, <code>username</code>, and <code>branch_id</code> from <code>users</code>, and a <code>branch_name</code> (not present in <code>users</code>) and a <code>parentBranch_id</code> from <code>branches</code> table.</p>
  <p>Now the organizational chart can be structured in a couple of layers, so as of now <code>branch_id</code> of a user, is a direct branch to which the user is assigned (ex. Finance), but the <code>parentBranch_id</code> is a level above that (ex. Company X). But for example, if there's one more layer, under Finance (ex. Bookkeeping), <code>parentBranch_id</code> would be Finance's <code>branch_id</code>. What I need, is to see the highest level of the structure as the <code>parentBranch_id</code> at all times. So in the case of Bookkeeping as <code>branch_id</code> I want to see Company X as <code>parentBranch_id</code>.</p>
  <p>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?</p>
  <p>My thinking: The function has to constantly check the <code>parentBranch_id</code>. IF NULL, then pass the <code>branch_id</code> as <code>parentBranch_id</code>. IF NOT NULL then look up the <code>parentBranch_id</code> value in the <code>branch_id</code> column - until it's NULL.</p>