title: Get high ranking parent matching a specific rule
body: <p>I'll start by explaining the structure.</p>
  
  <p>The 2 relevant tables are t_Users, and rel_UserParent.</p>
  
  <p>Here's how t_Users looks like:</p>
  
  <pre><code>+-----+-------------+------------+
  | Id  |  FullName   | RegionName |
  +-----+-------------+------------+
  | 100 | Jack Harp   | NULL       |
  | 101 | Dean Evan   | NULL       |
  | 103 | James Col   | North      |
  | 104 | John Smith  | South      |
  | 105 | Some dude   | NULL       |
  |   1 | MASTERLORD  | NULL       |
  +-----+-------------+------------+
  </code></pre>
  
  <p>Here's how rel_UserParent looks like:</p>
  
  <pre><code>+--------+----------+
  | UserNo | ParentNo |
  +--------+----------+
  |    100 |      101 |
  |    101 |      103 |
  |    105 |      104 |
  |    103 |        1 |
  |    104 |        1 |
  +--------+----------+
  </code></pre>
  
  <p>What I want to do, is create a view that will fetch me the regionName of any given user's parent, the problem is that the number of parents is dynamic. It could be 1 parent and could be 3.</p>
  
  <p>Every 2nd to top level parent meets one condition which is RegionName containing a value, basically if the RegionName is NULL then its not a 2nd to top level user.</p>
  
  <p>I tried using these similar questions for reference but to no avail.</p>
  
  <p><a href="https://stackoverflow.com/questions/17703863/finding-top-level-parent-of-each-row-of-a-table-sql-server-2008">Finding Top level parent of each row of a table [SQL Server 2008]</a></p>
  
  <p><a href="https://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql">Finding a Top Level Parent in SQL</a></p>
  
  <p>Just to be very clear, the expected result should be something along the lines of:</p>
  
  <pre><code>+-----+------------+
  | Id  | RegionName |
  +-----+------------+
  | 100 | North      |
  | 101 | North      |
  | 103 | North      |
  | 104 | South      |
  | 105 | South      |
  +-----+------------+
  </code></pre>