SQL Server 2008 - Get the bottom most linked record -

i have following data in sql server database:

id | name | linkedid ---+------+---------- 1  |    | 1 2  | b    | 2 3  | c    | 1 4  | d    | 3 5  | e    | 4 

now want write stored procedure in following record should shown:

note: linkedid has id associated name.

for example: "c" associated "a"

id | name | linkedid ---+------+--------- 1  |    | 1 2  | b    | 2 3  | c    | 1 4  | d    | 1 //here instead of showing 3, showed 1 bottom value in tree 5  | e    | 1 //same case above 

problem:

for scenario according limited knowledge can think of using joins (left, inner) won't enough in case bottom linked id of it.

edit (output):

i want items associated (directly , indirectly) item "c"

id | name |  ---+------+ 3  | c    |  4  | d    |  5  | e    |  

you use recursive function

a simple explain, recursive function using cte common table expression uses on caculating. includes:

1. invocation of routine.

the first invocation of recursive cte consists of 1 or more cte_query_definitions joined union all, union, except, or intersect operators.

because these query definitions form base result set of cte structure, referred anchor members.

cte_query_definitions considered anchor members unless reference cte itself.

all anchor-member query definitions must positioned before first recursive member definition, , union operator must used join last anchor member first recursive member.

2. recursive invocation of routine.

the recursive invocation includes 1 or more cte_query_definitions joined union operators reference cte itself. these query definitions referred recursive members.

3. termination check.

the termination check implicit; recursion stops when no rows returned previous invocation.

reference link: recursive query using cte

simple example of recursive cte cte sql server

declare @sampledata table (id int, name varchar(10), linkedid int)  insert @sampledata  values (1, 'a', 1), (2, 'b', 2),  (3, 'c', 1),(4, 'd', 3),(5, 'a', 4)  ;with temp  (    select sd.id, sd.name,  sd.id rootid     @sampledata sd sd.linkedid = sd.id -- invocation of routine, in case it's root node of tree.     union    -- recursive invocation of routine    select sd.id, sd.name,  t.rootid rootid      temp t    inner join @sampledata sd on sd.linkedid = t.id , sd.linkedid <> sd.id    -- termination check: sd.linkedid = t.id , sd.linkedid <> sd.id.     -- make recursive query not infinitive loop ) select  t.id, t.name, t.rootid linkedid temp t option (maxrecursion 0) -- option remove recursive max depth, default 100. 

demo link: rextester

for new output, change the first invocation of recursive cte

;with temp  (    select sd.id, sd.name,  sd.id rootid     @sampledata sd sd.id = 3     union    -- recursive invocation of routine    select sd.id, sd.name,  t.rootid rootid      temp t    inner join @sampledata sd on sd.linkedid = t.id , sd.linkedid <> sd.id    -- termination check: sd.linkedid = t.id , sd.linkedid <> sd.id.     -- make recursive query not infinitive loop ) select  t.id, t.name, t.rootid linkedid temp t option (maxrecursion 0) -- option remove recursive max depth, default 100.