1

Suppose I have a table 'prlines' with a self relation on adjacent records:

id | prev_id
------------
1  | NULL
2  | NULL
3  | 1
4  | 2
5  | 3
6  | 5

I want to get all connected IDs (previous/next) of a certain record. For example:

SELECT `prev_id` FROM `prlines` ... WHERE id = 5;

Should produce this output:

prev_id
-------
3
1
6

What I am doing currently is making a while loop in python that generates multiple queries to follow the relationship for each record. Any ideas to achieve this in a single mysql query?

sandrows
  • 303
  • 2
  • 12

1 Answers1

1

You can use a recursive cte:

with recursive cte(p, c, f) as (
   select p.*, p.prev_id = 5 from prlines p where p.id = 5 or p.prev_id = 5
   union all
   select p.*, c.f from cte c join prlines p on case when c.f then p.prev_id = c.p else p.id = c.c end
)
select case when f then p else c end prev_id from cte where c is not null order by f;

Output:

prev_id
3
1
6
7

See demo.

Ajax1234
  • 69,937
  • 8
  • 61
  • 102
  • Very nice, however can it crawl forward as well? it only goes forward one step. If I add a new row (id:7, prev_id:6) it should be included, but it is not. – sandrows Mar 19 '21 at 09:03