The point in this case is that the starting point is the same for a line, let’s say a train line. The line starts on the same point, but ends on different end points. The services depends on the line where they are riding.
I’ve added the extra requested information, this could be extended with more lines.
Example table:
Initial situation:
Name Next Result 1000 1100 1 1100 1200 2 1100 2000 2 1200 1300 3 2000 3000 3 3000 4000 4 1300 1400 4 1400 1500 5 4000 5000 5
Expected result:
Name Next Result 1000 1100 1 1100 1200 2 1200 1300 3 1300 1400 4 1400 1500 5 1000 1100 1 1100 2000 2 2000 3000 3 3000 4000 4 4000 5000 5
If one extra row is added to the example input, the output would change as follows…
Example table:
Initial situation:
Name Next Result 1000 1100 1 1100 1200 2 1100 2000 2 1200 1300 3 2000 3000 3 3000 4000 4 1300 1400 4 1400 1500 5 4000 5000 5 4000 4100 5 -- Additional single row
Expected result:
Name Next Result 1000 1100 1 1100 1200 2 1200 1300 3 1300 1400 4 1400 1500 5 1000 1100 1 1100 2000 2 2000 3000 3 3000 4000 4 4000 5000 5 1000 1100 1 -- Everything from here is additional, caused by the one additional input row 1100 2000 2 2000 3000 3 3000 4000 4 4000 4100 5
Advertisement
Answer
You appear to be describing a tree structure…
/- 1200 - 1300 - 1400
1000 - 1100
- 2000 - 3000 - 4000
The results would then be both (all) paths from the root node.
WITH
leaf AS
(
SELECT *
FROM nodes
WHERE NOT EXISTS (SELECT *
FROM nodes AS lookup
WHERE lookup.name = nodes.next
)
),
recursed_path AS
(
SELECT
*,
next AS branch_name,
0 AS level
FROM
leaf
UNION ALL
SELECT
root.*,
leaf.branch_name,
level + 1
FROM
recursed_path AS leaf
INNER JOIN
nodes AS root
ON root.next = leaf.name
)
SELECT
*
FROM
recursed_path
ORDER BY
branch_name,
level DESC
EDIT:
Minor corrections made, and this link to a working demo…