Calculate a column value backwards over a series of previous rows/RECURSIVE/CONNECTED BY

need your help. I guess/hope there is a function for that. I found “CONNECT DBY” and “WITH RECURSIVE AS …” but it doesn’t seem to solve my problem.

GIVEN TABLES:

Table A

+------+------------+----------+
| id   |   prev_id  |  date    |
+------------------------------+
| 1    |            | 20200101 |
| 23   |     1      | 20200104 |
| 34   |     23     | 20200112 |
| 41   |     34     | 20200130 |
+------------------------------+

Table B

+------+-----------+
| ref_id  |  key   |
+------------------+
|    41   |  abc   |
+------------------+

(points always to the lates entry in table “A”. Update, no history)

Join Statement:

SELECT id, prev_id, key, date FROM A LEFT OUTER JOIN B ON B.ref_id = A.id

GIVEN psql result set:

+------+------------+----------+-----------+
| id   |   prev_id  |    key   |   date    |
+------------------------------+-----------+
| 1    |            |          | 20200101  |
| 23   |     1      |          | 20200104  |
| 34   |     23     |          | 20200112  |
| 41   |     34     |   abc    | 20200130  |
+------------------------------+-----------+

DESIRED output:

+------+------------+----------+-----------+
| id   |   prev_id  |    key   |   date    |
+------------------------------+-----------+
| 1    |            |   abc    | 20200101  |
| 23   |     1      |   abc    | 20200104  |
| 34   |     23     |   abc    | 20200112  |
| 41   |     34     |   abc    | 20200130  |
+------------------------------+-----------+

The rows of the result set are connected by columns ‘id’ and ‘prev_id’. I want to calculate the “key” column in a reasonable time. Keep in mind, this is a very simplified example. Normally there are a lot of more rows and different keys and id’s

Answer

I understand that you want to bring the hierarchy of each row in tableb. Here is one approach using a recursive query:

with recursive cte as (
    select a.id, a.prev_id, a.date, b.key
    from tablea a
    inner join tableb b on b.ref_id = a.id
    union all
    select a.id, a.prev_id, a.date, c.key
    from cte c
    inner join tablea a on a.id = c.prev_id
)
select * from cte