By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl (id int, parent int, child int, level int, description text);
insert into tbl values (100, NULL, 10, 1, "Level 1"), (200, 10, 20, 2, "Level 2"), (300, 20, 30, 3, "Level 3"), (400, 30, 40, 4, "Level 4"), (101, NULL, 11, 1, "Lvl 1"), (201, 11, 21, 2, "Lvl 2");
create table fields (id int, field1 int, field2 int, parent int, child int, level int, description text);
insert into fields values (1, 123, 456, 30, 40, 4, "Level 4");
with recursive cte as (
select t.* from tbl t where t.level = 4
union all
select t.* from cte c join tbl t on t.child = c.parent
)
select f.id, f.field1, f.field2, c.level, c.description from cte c cross join fields f where f.level = 4
| id | field1 | field2 | level | description |
|---|---|---|---|---|
| 1 | 123 | 456 | 4 | Level 4 |
| 1 | 123 | 456 | 3 | Level 3 |
| 1 | 123 | 456 | 2 | Level 2 |
| 1 | 123 | 456 | 1 | Level 1 |