How to get the complete path with CTE

I have a table like below:

I want to get the ID path:

1 --> 5 --> 8
2
3 --> 6 --> 9 --> 10
4 --> 7

and I tried this:

WITH RECURSIVE path_cte AS (
  SELECT ID, NextID, ID::TEXT AS Path
  FROM t1
  WHERE NextID IS NULL
  UNION ALL
  SELECT t1.ID, t1.NextID, t1.ID || ' --> ' || cte.Path
  FROM t1
  JOIN path_cte cte ON t1.NextID = cte.ID
)
SELECT Path
FROM path_cte
ORDER BY ID;

but I got the output:

1 --> 5 --> 8
2
3 --> 6 --> 9 --> 10
4 --> 7
5 --> 8
6 --> 9 --> 10
7
8
9 --> 10
10

I don’t want to get those incomplete paths, but I don’t know how to achieve that.

The path is based on ID and NextID, if NextID is NULL, the path ends, and this ID is the end of the path, the next is to trace forward to get a complete path.

If an ID has neither a preceding ID nor a subsequent ID, it is also considered a path.

The database I use is compatible with PostgreSQL syntax, so you can also use PostgreSQL to demo, thanks 🙂

You need to sign in to view this answers