I have a table like below:
ID | NextID |
---|---|
1 | 5 |
2 | NULL |
3 | 6 |
4 | 7 |
5 | 8 |
6 | 9 |
7 | NULL |
8 | NULL |
9 | 10 |
10 | NULL |
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