SQL - Select child items in the hierarchy along with parent items

Question

In my database, I have Divisions of different levels structured into a hierarchy:

• Division 1 (Level 0)
  • Division 2 (level 1)
    • Division 3 (level 2)
      • Division 4 (level 3)
    • Division 5 (level 2)
    • Division 6 (level 2)
  • Division 7 (level 1)
• Division 8 (Level 0)
  • Division 9 (Level 1)
  • Division 10 (Level 1)
    • Division 11 (Level 2)

Using recursion and CTEs, I produce a table with all divisions in the database ordered by its hierarchy path (float value for convenience):

DivisionHierarchy

| DivisionID | DivisionName | ParentID | Level | SortOrder |
|          1 | Division 1   | NULL     | 0     | 1         |
|          2 | Division 2   | 1        | 1     | 1.1       |
|          3 | Division 3   | 2        | 2     | 1.11      |
|          4 | Division 4   | 3        | 3     | 1.111     |
|          5 | Division 5   | 2        | 2     | 1.12      |
|          6 | Division 6   | 2        | 2     | 1.13      |
|          7 | Division 7   | 1        | 1     | 1.2       |
|          8 | Division 8   | NULL     | 0     | 2         |
|          9 | Division 9   | 8        | 1     | 2.1       |
|         10 | Division 10  | 8        | 1     | 2.2       |
|         11 | Division 11  | 10       | 2     | 2.21      |

Then I have another table with selected Divisions:

SelectedDivisions

| DivisionID |
|          3 |
|         10 |

Given selected Divisions, I need to filter DivisionHierarchy table to show:

• selected Divisions;
• all child Divisions for selected Divisions (including nested children);
• Divisions that are parent to the selected ones, but only those that link to them in their structure path.

So, for DivisionID IN (3, 10), the output should be:

• Division 1 (Level 0)
  • Division 2 (level 1)
    • Division 3 (level 2)
      • Division 4 (level 3)
• Division 8 (Level 0)
  • Division 10 (Level 1)
    • Division 11 (Level 2).

Output:

| DivisionID | DivisionName | ParentID | Level | SortOrder |
|          1 | Division 1   | NULL     | 0     | 1         |
|          2 | Division 2   | 1        | 1     | 1.1       |
|          3 | Division 3   | 2        | 2     | 1.11      |
|          4 | Division 4   | 3        | 3     | 1.111     |
|          8 | Division 8   | NULL     | 0     | 2         |
|         10 | Division 10  | 8        | 1     | 2.2       |
|         11 | Division 11  | 10       | 2     | 2.21      |

Could you please tell me how can I achieve this?

Answer 1

I have achieved the desired result by rewriting the last part as:

;WITH CTE_Parents
AS
(
    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN @SelectedDivisions s
            ON h.DivisionID = s.DivisionID

    UNION ALL

    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN CTE_Parents c ON c.DivisionID = h.ParentID
),
CTE_Children
AS
(
    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN @SelectedDivisions s
            ON h.DivisionID = s.DivisionID

    UNION ALL

    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN CTE_Children c ON c.ParentID = h.DivisionID
)

SELECT * FROM CTE_Parents
UNION
SELECT * FROM CTE_Children
ORDER BY SortOrder

Answer 2

I've tried to accomplish it with the script below, however they are two query results which should be combined (and I don't know how). But maybe it points you in the right direction.

DECLARE @DivisionHierarchy TABLE
(
    DivisionID TINYINT NOT NULL,
    DivisionName VARCHAR(20) NOT NULL,
    ParentID TINYINT,
    [Level] TINYINT NOT NULL,
    SortOrder VARCHAR(20) NOT NULL
)
INSERT INTO @DivisionHierarchy VALUES (1, 'Division 1', NULL, 0, '1');
INSERT INTO @DivisionHierarchy VALUES (2, 'Division 2', 1, 1, '1.1');
INSERT INTO @DivisionHierarchy VALUES (3, 'Division 3', 2, 2, '1.11');
INSERT INTO @DivisionHierarchy VALUES (4, 'Division 4', 3, 3, '1.111');
INSERT INTO @DivisionHierarchy VALUES (5, 'Division 5', 2, 2, '1.12');
INSERT INTO @DivisionHierarchy VALUES (6, 'Division 6', 2, 2, '1.13');
INSERT INTO @DivisionHierarchy VALUES (7, 'Division 7', 1, 1, '1.2');
INSERT INTO @DivisionHierarchy VALUES (8, 'Division 8', NULL, 0, '2');
INSERT INTO @DivisionHierarchy VALUES (9, 'Division 9', 8, 1, '2.1');
INSERT INTO @DivisionHierarchy VALUES (10, 'Division 10', 8, 1, '2.2');
INSERT INTO @DivisionHierarchy VALUES (11, 'Division 11', 10, 2, '2.21');

SELECT * FROM @DivisionHierarchy ORDER BY DivisionID;

DECLARE @SelectedDivisions TABLE
(
    DivisionID TINYINT
);
INSERT INTO @SelectedDivisions VALUES (3);
INSERT INTO @SelectedDivisions VALUES (10);


;WITH CTE
AS
(
    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN @SelectedDivisions s
            ON h.DivisionID = s.DivisionID

    UNION ALL

    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN CTE c ON c.DivisionID = h.ParentID
)
SELECT * FROM CTE ORDER BY DivisionID

;WITH CTE
AS
(
    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN @SelectedDivisions s
            ON h.DivisionID = s.DivisionID

    UNION ALL

    SELECT h.*
    FROM @DivisionHierarchy h
        INNER JOIN CTE c ON c.ParentID = h.DivisionID
)
SELECT * FROM CTE ORDER BY DivisionID