CODEWITHSUNDEEP
sqlhierarchical-datarecursive-query
mariocatch
mariocatch

Reputation: 8673

Recurse through mappings in SQL

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.

Upvotes: 0

Views: 59

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42753

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)

Upvotes: 1

Related Questions