Inner Join until null on self-referencing tables I have the following SQL Table:

 OrganizationalUnit
    ID - PK
    ParentOrganizationalUnit - FK
    Title
    Department_Code (optional)
    Division_Code (optional)

 Person
    ID - PK
    OrganizationalUnitID - FK
    FirstName
    LastName


And for the sake of this example, these tables appear as follows:

OrganizationalUnit:
0 | (null) | Information Technology |  101   | (null)
1 | 0      | Database Management    | (null) | 4000
2 | (null) | Human Resources        |  102   | (null)
3 | 2      | Recruitment            | (null) | 4000
4 | (null) | Finance                | (null) | (null)
5 | 1      | SQL                    | (null) | (null)

People
0 | 0 | John    | Doe
1 | 0 | Jane    | Doe
2 | 1 | Josh    | Doe
3 | 1 | Jack    | Doe
4 | 3 | Other   | Doe
5 | 4 | Finance | Doe
6 | 5 | Odd     | Doe


I need to run a query so that I obtain the following output:

Output
PersonID | OrganizationalUnitID | Department_Code | Division_Code | First Name | last Name
0        |         0            |       101       |       (null)  |  John      |  Doe
1        |         0            |       101       |       (null)  |  Jane      |  Doe
2        |         1            |       101       |       4000    |  Josh      |  Doe
3        |         1            |       101       |       4000    |  Josh      |  Doe
4        |         3            |       102       |       4000    |  Other     |  Doe
5        |         4            |      (null)     |       (null)  |  Finance   |  Doe
6        |         5            |      101        |       4000    |   Odd       |  Doe


The department_code and division_code fields are used for association of OrganizationalUnits with another database. This other database is only capable of tracking up to 2 levels of hierarchy (department and division) whereas, I have been asked to track an infinite level of hierarchy, but to map the first level to departments and the second level to divisions. I have no idea where to start to write a query that could do this. I may have to redesign my ERD.
There is a chapter in SQL Server called: Recursive Queries Using Common Table Expressions. It is very well explained and has a very clear and simple example.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186243(v=sql.105)

Based on this documentation, my query solution is as follows (tested OK):

WITH CTE_RecursiveOU (ID, ParentID, DepCode,DivCode)
AS
(
-- Anchor member definition
    SELECT 
        u.ID as ID,
        u.ParentOrganizationalUnit as ParentID, 
        u.Department_Code as  DepCode,
        u.Division_Code as DivCode
    FROM dbo.OrganizationalUnit AS u
    WHERE u.ParentOrganizationalUnit IS NULL
    UNION ALL
-- Recursive member definition
    SELECT          
        u.ID as ID,
        u.ParentOrganizationalUnit as ParentID, 
        r.DepCode as  DepCode,
        ISNULL(r.DivCode,u.Division_Code) as DivCode
    FROM dbo.OrganizationalUnit AS u
    INNER JOIN CTE_RecursiveOU AS r
        ON u.ParentOrganizationalUnit = r.ID
)
-- Statement that executes the CTE
SELECT  
    p.ID as PersonID, 
    c.ID as OrganizationalUnitID,
    c.DepCode as Department_Code, 
    c.DivCode as Division_Code,
    p.FirstName,
    p.LastName
FROM CTE_RecursiveOU AS c
INNER JOIN dbo.Person AS p
    ON p.OrganizationalUnitID = c.ID;

GO