0

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.

1
  • 1
    Could you also clarify what database server and version you're on (in the tags of your post)? Aug 16 '18 at 18:20
2

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
1
  • 1
    THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Your code was perfect! Only change I made was r.DepCode as DepCode to ISNULL(r.DepCode, u.Department_Code.
    – S. Walker
    Aug 18 '18 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.