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
Advertisement
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