Recursivly Join Closest Ancestor/Parent To Record In Same Table?-CodePudding

As-per the title of this question I am trying to have a record join with another record recursively in the same table with a specific property (whichever parent record closest) but am not entirely sure how to go about this one. Example SQLFiddle:

http://sqlfiddle.com/#!18/0c5fb/2

The end result I am trying to achieve is something like this where each row knows its closest ancestor where NeededElement is true:

ElementID	ElementName	NeededElement	ParentElementID	ClosestNeededElementID
1	Root Element	true	(null)	(null)
2	Child Element A	true	1	1
3	Child Element B	false	1	1
4	Child Element AA	false	2	2
5	Child Element AB	false	2	2
6	Child Element BA	false	3	1
7	Child Element BB	false	3	1

Many thanks in advance!

CodePudding user response：

You can keep a running column for the closest parent by using a conditional:

with cte(id, name, n_elem, p, l) as (
   select *, null from testelements where parentelementid is null
   union all
   select t.*, case when c.n_elem = 1 then c.id else c.l end
   from cte c join testelements t on t.parentelementid = c.id
)
select * from cte;

CodePudding user response：

You may try the following modification to your shared query.

WITH CTE AS
(
  SELECT
  ElementID,
  ElementName,
  NeededElement,
  ParentElementID,
  ParentElementID as NextParentElementID, 
  CAST(NULL AS INT) as ClosestNeededElementID
  FROM TestElements
  
  UNION ALL
  
  SELECT
      CTE.ElementID,
      CTE.ElementName,
      CTE.NeededElement,
      CTE.ParentElementID,
      TestElements.ParentElementID as NextParentElementID,
      CASE 
          WHEN TestElements.NeededElement=1 THEN TestElements.ElementID
      END AS ClosestNeededElementID
  FROM CTE
  INNER JOIN TestElements ON CTE.NextParentElementID = TestElements.ElementID 
  WHERE CTE.ClosestNeededElementID IS NULL 
)
SELECT 
   ElementID,
  ElementName,
  NeededElement,
  ParentElementID,
  ClosestNeededElementID
FROM CTE
WHERE (ParentElementId IS NULL) OR (ClosestNeededElementId IS NOT NULL)
ORDER BY ElementID ASC

View working demo fiddle

Let me know if this works for you.