A->B->C relationship management in a 2 column sqlite database-CodePudding

Hello Everyone and thank you in advance for the help.

I have setup a correspondance table that looks like this

Old	New
A	B
B	C
A	D
C	D

Of course this is a log of correspondance between one state to another at multiple points in time. I am trying to transform this data into a "usable format" by flattening out the relationships between old and new to get to something like this:

Old	New
A	D
B	D
C	D

Does anyone have any idea how to do something like this in SQL? Please keep in mind the iterations can run 7 levels deep. Thank you very much

CodePudding user response：

Use a recursive CTE:

WITH cte AS (
  SELECT old, new, 1 level FROM tablename
  UNION ALL
  SELECT c.old, t.new, level   1
  FROM tablename t INNER JOIN cte c
  ON c.new = t.old
)
SELECT DISTINCT old, 
       FIRST_VALUE(new) OVER (PARTITION BY old ORDER BY level DESC) new 
FROM cte
ORDER BY old;

See the demo.