Recurse through mappings in SQL

Question

I have a table like this:

[Mappings]

Parent | Child
---------------
   4   |   10
   1   |   4

In SQL, I'm trying to run a query with an input of 10, and get back all of its parents up the chain... so 4 and 1 in this case.

If I run this with an input of 4, it would return 1.

I'm thinking I need to use a common table expression (CTE), but the syntax is throwing me off.

Its ansvered here : [https://stackoverflow.com/questions/28170635/get-all-parents-for-a-child](https://stackoverflow.com/questions/28170635/get-all-parents-for-a-child) — Henrik Clausen, Jun 01 '17 at 15:52

Oto Shavadze · Accepted Answer · 2017-06-01T16:16:15.800

I doubt you use sql server, if yes, then you need something like this:

create table #test(
Parent int,
Child int
);

insert into  #test
values
(4   ,   10),
(1   ,   4),
(10   ,   12);

with rec as (
    select #test.*, 1 as lvl from #test where Child = 10
    union all
    select #test.*, lvl + 1 as lvl from #test    
    inner join rec   
    on #test.Child = rec.Parent
)
select parent, lvl from rec
OPTION (MAXRECURSION 0)

Also maybe useful to see level column (lvl in this case)

Recurse through mappings in SQL

1 Answers1