Recursive query - select only nodes where leaf nodes represent active data

Question

Given the following recursive query:

WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS
(
   -- Base case
   SELECT
      DepartmentId,
      Name,
      IsInactive,
      IsSpecial,
      ParentId,
      1 as HierarchyLevel
   FROM StoreDepartment
   WHERE ParentId IS NULL

   UNION ALL

   -- Recursive step
   SELECT
      d.DepartmentId,
       d.Name,
      d.IsInactive,
      d.IsSpecial,
      d.ParentId,
      dh.HierarchyLevel + 1 AS HierarchyLevel
   FROM StoreDepartment d
      INNER JOIN DepartmentHierarchy dh ON
         d.ParentId = dh.DepartmentId
) SELECT * FROM DepartmentHierarchy 

I can select data that looks like this:

DepartmentId, Name, IsInactive, IsSpecial, ParentId, HeirarchyLevel
1, Store, 0, 0, NULL, 1
2, Main Department 1, 0, 1, 2
3, Main Department 2, 0, 1, 2
4, Sub For Main 1, 0, 2, 3

Also, suppose there is a table with DepartmentId and ItemId (for example: DepartmentItemRelationship). Leaf nodes from the department hierarchy are merged with the elements here.

I want my recursive query to return only nodes (at any level) that have at least one node sheet under them with a match in the table of relations between departments and elements. These nodes can be 6 or 7 levels down, so I'm not sure how to change my query to include them.

Thanks Kyle

Answer 1

, . , DepartmentItemRelationship. , , , , , .

- :

    WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS
(
   -- Base case
   SELECT
      '/'+cast( DepartmentId as varchar(max)) as [path]
      DepartmentId,
      Name,
      IsInactive,
      IsSpecial,
      ParentId,
      1 as HierarchyLevel
   FROM StoreDepartment
   WHERE ParentId IS NULL

   UNION ALL

   -- Recursive step
   SELECT
      dh.[path] +'/'+ cast( d.DepartmentId as varchar(max)) as [path]
      d.DepartmentId,
      d.Name,
      d.IsInactive,
      d.IsSpecial,
      d.ParentId,
      dh.HierarchyLevel + 1 AS HierarchyLevel
   FROM StoreDepartment d
      INNER JOIN DepartmentHierarchy dh ON
         d.ParentId = dh.DepartmentId
   where exists ( select top 1 1 
                  from DepartmentItemRelationship di
                  where di.DepartmentId = d.DepartmentId )
) 
SELECT * 
FROM DepartmentHierarchy dh
where exists ( select top 1 1 
               from DepartmentHierarchy 
               where charindex('/'+dh.DepartmentID+'/',[path]) > 0) 

Answer 2

, , ?

. , , , .

WITH LeafNodeParents AS
(
    SELECT DISTINCT ParentId
    FROM StoreDepartment
    WHERE DepartmentId NOT IN
    (
        SELECT DISTINCT ParentId FROM StoreDepartment
    )
)
SELECT d.DepartmentId, d.Name, d.IsInactive, d.IsSpecial, d.ParentId
FROM LeafNodeParents p
INNER JOIN StoreDepartment d
    ON d.DepartmentId = p.ParentId

, , . , . , , ; , , ( ).