Get high ranking parent matching a specific rule

Question

I'll start by explaining the structure.

The 2 relevant tables are t_Users, and rel_UserParent.

Here's how t_Users looks like:

+-----+-------------+------------+
| 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       |
+-----+-------------+------------+

Here's how rel_UserParent looks like:

+--------+----------+
| UserNo | ParentNo |
+--------+----------+
|    100 |      101 |
|    101 |      103 |
|    105 |      104 |
|    103 |        1 |
|    104 |        1 |
+--------+----------+

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.

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.

I tried using these similar questions for reference but to no avail.

Finding Top level parent of each row of a table [SQL Server 2008]

Finding a Top Level Parent in SQL

Just to be very clear, the expected result should be something along the lines of:

+-----+------------+
| Id  | RegionName |
+-----+------------+
| 100 | North      |
| 101 | North      |
| 103 | North      |
| 104 | South      |
| 105 | South      |
+-----+------------+

Answer 1

If I understand correctly, you can just use a recursive CTE:

with cte as (
      select rup.userno, u.region, 1 as lev
      from rel_UserParent rup join
           t_users u
           on rup.userno = u.id
      where u.region is not null
      union all
      select rup.userno, cte.region, cte.lev + 1 as lev
      from cte join
           rel_UserParent rup
           on rup.parent = cte.userno
    )
select *
from cte;

This starts at the users who do have regions. It then adds their hierarchies assigning the appropriate regions to them.

Here is a SQL Fiddle, or the runnable code:

with rel_UserParent as (
      select *
      from (values (100, 101), (101, 103), (105, 104), (103, 1), (104, 1)
           ) v(UserNo, ParentNo)
     ),
     t_users as (
      select *
      from (values (100, 'Jack Harp', NULL),
                  (101, 'Dean Evan', NULL),
                  (103, 'James Col', 'North'),
                  (104, 'John Smith', 'South'),
                  (105, 'Some dude', NULL),
                  (1, 'MASTERLORD', NULL)
           ) v(id, FullName, RegionName)
     ),
     cte as (
      select rup.userno, u.regionname, 1 as lev
      from rel_UserParent rup join
           t_users u
           on rup.userno = u.id
      where u.regionname is not null
      union all
      select rup.userno, cte.regionname, cte.lev + 1 as lev
      from cte join
           rel_UserParent rup
           on rup.parentno = cte.userno
    )
select *
from cte;