0

Still learning SQL :) This time I'd like to a 'linked list' walk from a table I guess using CTE. Despite all the example on the web I could not find one simple example I could start from then peek and poke from there.

Here is my table

create table yo (id integer, nx integer)
select * from yo
id          nx
----------  ----------
1           5
2           4
3           7
4           9
5           3
6           0
7           0
8           6
9           8

I'd like to get a list of 'id','nx' from yo following the next link 'nx' given a start 'id'

So a start 'id' of 1 would produce

id          nx
----------  ----------
1           5
3           7
5           3
7           0

Note that 0 is a end marker.

I can't find the magic SQL for doing this Thanx in advance Cheers, Phi

Phi
  • 735
  • 7
  • 22
  • My table is garbled in my initial question dunno ho w to edit my inial post – Phi Apr 14 '15 at 09:26
  • This is the same as the typical parent/child relationship. What exactly is the problem? – CL. Apr 14 '15 at 11:20
  • 1
    Fixed my table display in main question (sorry about that). CL there is no problem, (I think) but it is me beiin a beginner, I tried to 'duplicate' example given on the net, but there are too complicate and I can't make it works. I mean I am not able to craft the SQL statement to obtain a list of (id,nx) starting at some point in the chain. – Phi Apr 14 '15 at 12:21
  • Ha the search keyword here is parent/child, I keep searching for 'list' I found a stackoverflow response will look if it helps. http://stackoverflow.com/questions/19914472/cte-to-get-all-children-descendants-of-a-parent – Phi Apr 14 '15 at 12:26

1 Answers1

2

The first row of the list is easy:

SELECT id, nx
FROM yo
WHERE id = 1

If the nx column of the previous entry is available as list.nx, the next entry can be returned with this query:

SELECT yo.id, yo.nx
FROM yo
JOIN list ON yo.id = list.nx

Then just plug these together:

WITH RECURSIVE list(id, nx) AS (
  SELECT id, nx
  FROM yo
  WHERE id = 1
  UNION ALL
  SELECT yo.id, yo.nx
  FROM yo
  JOIN list ON yo.id = list.nx
)
SELECT * FROM list

(This stops automatically because there is no row where id is zero; otherwise, you could add a WHERE list.nx != 0.)

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanx CL, I was almost there schoking on the join. It runs like a champs, and I can handle circular list as well with the WHERE list.nx!=1 (assuming orignal chanded to have entry 7 pointing back to 1 instead of beiing 0. Again thanx. – Phi Apr 14 '15 at 13:02
  • A better way to prevent an endless loop is using UNION instead of UNION ALL (see the [documentation](http://www.sqlite.org/lang_with.html)). – CL. Apr 14 '15 at 14:18