I would probably need more information to be sure that this is the best approach, but based on what you have provided, I would do the following: Perform an outer-join between the table and itself, setting one sides parent equal to the other sides child. What this will do is return parents of parents if they exist. For example, row two would match nothing because 1 has no parent, but row three would return 1 as 2, the parent of row three has it's own parent. Then, if the new parent field is not null, replace the original parent with the new one. Then you push that list back into the loop and iterate until every parent is the absolute base parent. Now, all you need is a way to verify if each new parent is parent-less, as that is when you would want to stop iterating. You can probably achieve this by performing another join like you did initially and if there are no records in the J output, then you know that each parent has no parent of their own. This is a cool problem, I would love to hear about how you get it done. Good luck!
