Finding top parent using CTE

Question

Finding top parent using CTE

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Mark Fitzgerald-331224 (4/17/2012)
Matt, agreed there should be no presumption of a single ultimate parent in a hierarchy.
Answering you questions
Is it:
- node(s) which have the longest lineage -> in this case it does not matter whose has the longest lineage as long as a closed loop is not involved
- nodes which have the MOST descendant nodes -> not in this case, each node may have 1 or more children but only 1 parent, the amount of decendents does not matter
- nodes which aren't children of anyone else (possibly with a second requirement of having children themselves).-> not in this case either, the data may have a loop within a single node by a node having itself as a parent.
The request was to stop a long running CTE for parent and child originally and the OP had selected a set of data that could not loop and would succeed quickly. To get the recursion to bug out there must have been something else (over 100 levels intentionally or unintensionally).
I have seen this in lots of situations where the child node is edited to have a parent node regardless of whether the parent node exists (OP data node 10 did not exist originally), parent node = child node or a 2+ closed loop was created as discussed earlier.
Fitz

Non-trivial loops are invalid regardless (no one in a loop is ever the "top level": that's the definition of a loop). While it's important to identify that you have invalid data, no element in a loop can be identified as the top level parent until someone "breaks" the loop.

Assuming that's a given, there's no need to traverse the hierarchy: a simple select will find your results. Something like:

Select * from #t

where childID=ParentID --Assuming you're implementing trival loops to show "single" parents

or ChildID is null -- another way

or ParentID not in (select ChildID from #t)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 2

Matt Miller (#4) (4/17/2012)
Mark Fitzgerald-331224 (4/17/2012)
Matt, agreed there should be no presumption of a single ultimate parent in a hierarchy.
Answering you questions
Is it:
- node(s) which have the longest lineage -> in this case it does not matter whose has the longest lineage as long as a closed loop is not involved
- nodes which have the MOST descendant nodes -> not in this case, each node may have 1 or more children but only 1 parent, the amount of decendents does not matter
- nodes which aren't children of anyone else (possibly with a second requirement of having children themselves).-> not in this case either, the data may have a loop within a single node by a node having itself as a parent.
The request was to stop a long running CTE for parent and child originally and the OP had selected a set of data that could not loop and would succeed quickly. To get the recursion to bug out there must have been something else (over 100 levels intentionally or unintensionally).
I have seen this in lots of situations where the child node is edited to have a parent node regardless of whether the parent node exists (OP data node 10 did not exist originally), parent node = child node or a 2+ closed loop was created as discussed earlier.
Fitz
Non-trivial loops are invalid regardless (no one in a loop is ever the "top level": that's the definition of a loop). While it's important to identify that you have invalid data, no element in a loop can be identified as the top level parent until someone "breaks" the loop.
Assuming that's a given, there's no need to traverse the hierarchy: a simple select will find your results. Something like:
Select * from #t
where childID=ParentID --Assuming you're implementing trival loops to show "single" parents
or ChildID is null -- another way
or ParentID not in (select ChildID from #t)

The OP in this case had data where the loop was caused by an nth degree loop, unknown to him. He wanted a CTE to traverse the hierarchy in all cases and return a result not the max recursion error.

The code you gave would have identified the loop for this case, but not shown the extent of the loop, or returned the result for all search cases as per the OP request.

🙂

Fitz