Table valued function for parent id

Question

So I have a database table that looks like -

MemberId    LinkedMemberId
1           1
2           1
3           1
4           2
5           4
6           4

And I want to build a table value function where I can pass in 1, 2, 3, 4, 5 or 6 and it will return all of the values because they are linked.

Example - I pass in 4, so I want 2, my LinkedMemberId. Now because I have 2, I also want the LinkedMemberId of 1. And now because I have 1, I also want the MemberIds associated with LinkedMemberId of 1.

Is there already an algorithm to get this kind of data?

search around for "hierarchical structure" on tsql – George Menoutis Jun 17 '20 at 21:41 — George Menoutis, Jun 17 '20 at 21:41

score 0 · Answer 1 · answered Jun 17 '20 at 21:58

0

You seem to be looking for a hierarchical query that retrives all parents of a given node. A typical approach is a recursive common-table-expression:

with cte as (
    select LinkedMemberId, 1 lvl from mytable where memberId = ?
    union all
    select t.LinkedMemberId, c.lvl + 1
    from mytable t
    inner join cte c on c.LinkedMemberId = t.memberId
)
select * from cte

This gives you one row for each parent of the input memberId (which is represented by the ? in the query). As a bonus, the second column contains an incrementing integer value that represents the level of the parent in the hierarchy (starting at 1 for the first level parent).

answered Jun 17 '20 at 21:58

GMB

216,147
25
84
135

for the most part this works, but I don't have a termination check to get out of the recursive call. – duckmike Jun 18 '20 at 02:10
@duckmike If the termination issue is caused by cycles in your data then [this](https://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0/15081353#15081353) answer demonstrates a way to terminate the loops. – HABO Jun 18 '20 at 02:30

Table valued function for parent id

1 Answers1