sql - How do I trace former ids using a recursive query? -

i have table of provider information (providers) contains columns reporting_unit , predesessor. predesessor either null or contains reporting_unit that row used represent. need find current reporting_unit provider is. mean reporting_unit predesessor, reporting_unit current_reporting_unit predesessor.

i trying use recursive cte accomplish because of time there multiple links.

the table looks this:

create table providers (    reporting_unit text,    predesessor text );  insert providers values     (null, null),     ('are88', null),     ('99bx7', '99bx6'),     ('99bx6', '99bx5'),     ('99bx5', null) ; 

the results are:

reporting_unit | current_reporting_unit ---------------------------------------        '99bx5' | '99bx7'        '99bx6' | '99bx7' 

my current query :

with recursive current_ru (     select reporting_unit, predesessor     providers     predesessor null      union      select p.reporting_unit, p.predesessor     providers p          join current_ru cr          on p.reporting_unit = cr.predesessor     ) select * current_ru ; 

but isn't giving me results i'm looking for. have tried number of variations on query seem end in infinite loop. how

you should find relations in reverse order. add depth column find deepest link:

with recursive current_ru (reporting_unit, predesessor, depth) (     select reporting_unit, predesessor, 1     providers     predesessor not null union     select r.reporting_unit, p.predesessor, depth+ 1     providers p     join current_ru r     on p.reporting_unit = r.predesessor     ) select * current_ru;   reporting_unit | predesessor | depth  ----------------+-------------+-------  99bx7          | 99bx6       |     1  99bx6          | 99bx5       |     1  99bx6          |             |     2  99bx7          | 99bx5       |     2  99bx7          |             |     3 (5 rows) 

now switch 2 columns, change names, eliminate null rows , select deepest links:

with recursive current_ru (reporting_unit, predesessor, depth) (     select reporting_unit, predesessor, 1     providers     predesessor not null union     select r.reporting_unit, p.predesessor, depth+ 1     providers p     join current_ru r     on p.reporting_unit = r.predesessor     ) select distinct on(predesessor)      predesessor reporting_unit,      reporting_unit current_reporting_unit current_ru predesessor not null order predesessor, depth desc;   reporting_unit | current_reporting_unit  ----------------+------------------------  99bx5          | 99bx7  99bx6          | 99bx7 (2 rows)