;WITH cte as
(	SELECT CurrentId = s.StateID,
		Level = 1,
		Id1 = s.StateID,
		Id2 = convert(varchar(10), null),
		Id3 = convert(varchar(10), null)
	from State s
	--
	union all
	--
	select -- get regions below
		CurrentId = r.RegionID,
		Level = c.Level + 1,
		Id1 = c.Id1,
		Id2 = case when c.Level+1 < 2 then null when c.Level+1 = 2 then r.RegionID else c.Id2 END,
		Id3 = case when c.Level+1 < 3 then null when c.Level+1 = 3 then r.RegionID else c.Id3 END
	from region r
		join cte c 
			on r.StateID = c.CurrentId and r.Parent_RegionID is null -- under the state
			or
			r.Parent_RegionID = c.CurrentId -- under the other region
)
--select * from cte --<<< Try it
select c.Id1, c.Id2, c.Id3, b.Name, b.ProfileID
from cte c
	join Branch b on b.RegionID = c.CurrentId

SELECT
   b.BranchID
   ,(CASE
        WHEN b1.Grp1_Inherited = 1 THEN 
            CASE
                WHEN r1.Grp1_Inherited = 1 THEN s1.Grp1_V1
                ELSE r1.Grp1_V1            
            END
        ELSE b1.Grp1_V1
    END) AS Grp1_V1
   ,(CASE
        WHEN b1.Grp1_Inherited = 1 THEN 
            CASE
                WHEN r1.Grp1_Inherited = 1 THEN s1.Grp1_V2
                ELSE r1.Grp1_V2
            END
        ELSE b1.Grp1_V2
    END) AS Grp1_V2
   ,(CASE
        WHEN b1.Grp2_Inherited = 1 THEN 
            CASE
                WHEN r1.Grp2_Inherited = 1 THEN s1.Grp2_V1
                ELSE r1.Grp2_V1
            END
        ELSE b1.Grp2_V1
    END) AS Grp2_V1
FROM
    dbo.State AS s
INNER JOIN 
    dbo.Region AS r
    ON s.StateID = r.StateID
INNER JOIN 
    dbo.Branch AS b
    ON r.RegionID = b.RegionID
INNER JOIN 
    dbo.Profile AS s1
    ON s.ProfileID = s1.ProfileID
INNER JOIN 
    dbo.Profile AS r1
    ON r.ProfileID = r1.ProfileID
INNER JOIN 
    dbo.Profile AS b1
    ON b.ProfileID = b1.ProfileID
ORDER BY
    b.BranchID