How do I find the final "link in the chain" using a recursive CTE

Question

I'm close on this but missing something. How do I only get the first and last links in chains such as A->B, B->C? How do I just get A->C?

CREATE TEMP TABLE IF NOT EXISTS chains (
    cname TEXT PRIMARY KEY,
    becomes TEXT
);

INSERT INTO chains
VALUES
    ('A', NULL),
    ('B', 'C'),
    ('C', 'D'),
    ('D', 'E'),
    ('E', NULL)
;

WITH RECURSIVE
final_link AS (
SELECT
    chains.cname,
    chains.becomes
FROM
    chains

UNION

SELECT
    chains.cname,
    final_link.becomes
FROM
    chains
    INNER JOIN final_link
    ON chains.becomes = final_link.cname
)
SELECT * FROM final_link;

The results I would like are:

cname | becomes
------|--------
'B'   | 'E'
'C'   | 'E'
'D'   | 'E'

Answer 1

You can achieve this by starting the recursion only with the chain ends, not with all links, then iteratively prepending links as you are already doing:

WITH RECURSIVE final_link AS (
  SELECT cname, becomes
  FROM chains c
  WHERE (SELECT becomes IS NULL FROM chains WHERE cname = c.becomes)
UNION
  SELECT c.cname, fl.becomes
  FROM chains c
  INNER JOIN final_link fl ON c.becomes = fl.cname
)
SELECT * FROM final_link;

(Demo)