Search code examples
sqlsqliteselectwindow-functionsrecursive-query

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

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

Solution

  • 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.