"repair" root PK using recursive self joins

Question

Take the following fictional website data (0 specifies a missing value):

USER_ID	TRANSACTION_ID_PARENT	TRANSACTION_ID_CHILD
1	0	1
1	1	2
1	2	3
1	0	11
1	11	12

How do I add a column that "repairs"/generates a root primary key:

USER_ID	TRANSACTION_ID_PARENT	TRANSACTION_ID_CHILD	TRANSACTION_ID_ROOT
1	0	1	1
1	1	2	1
1	2	3	1
1	0	11	11
1	11	12	11

My hunch would be a recursive CTE grouped by USER_ID? How to achieve this in the IBM db2 dialect?

Maybe there are more elegant database specific variants of a recursive self join?

Answer 1

It can be done this way :

with table1 (USER_ID, TRANSACTION_ID_PARENT, TRANSACTION_ID_CHILD) as (
  VALUES
  (1, 0, 1),
  (1, 1, 2),
  (1, 2, 3),
  (1, 0, 11),
  (1, 11, 12)
),
rec (USER_ID, TRANSACTION_ID_PARENT, TRANSACTION_ID_CHILD, TRANSACTION_ID_ROOT) as (
  select USER_ID, TRANSACTION_ID_PARENT, TRANSACTION_ID_CHILD, TRANSACTION_ID_CHILD
  from table1 where TRANSACTION_ID_PARENT= 0
  union all
  select rec.USER_ID, table1.TRANSACTION_ID_PARENT, table1.TRANSACTION_ID_CHILD, rec.TRANSACTION_ID_ROOT
  from rec, table1 where (rec.user_id, rec.TRANSACTION_ID_CHILD) = (table1.USER_ID, table1.TRANSACTION_ID_PARENT)
)
select * from rec
order by USER_ID, TRANSACTION_ID_ROOT, TRANSACTION_ID_PARENT, TRANSACTION_ID_CHILD

USER_ID	TRANSACTION_ID_PARENT	TRANSACTION_ID_CHILD	TRANSACTION_ID_ROOT
1	0	1	1
1	1	2	1
1	2	3	1
1	0	11	11
1	11	12	11

fiddle