Mysql: Get all the linked rows in a table

Question

I have a table structure

kinda like this

id  |   title | next 
1   |  test   | 5
2   |  test   | 0
3   |  test   | 0
4   |  test   | 0
5   |  test   | 3

Now, as you see 1 points to next item 5 and 5 points to next item 3 and 3 denotes the end

I need a query, from which I can get 1, 5, 3 serially in one column and their title also

like

result | title
--------------
1      |  test
5      |  test
3      |  test
--------

please help. I dont even know how to get started at such query.

score 1 · Answer 1 · edited May 23 '17 at 11:55

1

edited May 23 '17 at 11:55

Community

answered Apr 05 '11 at 12:32

Frank Schmitt

I saw your link, but I am lost. I dont know how to implement it, in my case. May be a small example. – mrN Apr 05 '11 at 12:40

score 1 · Answer 2 · answered Apr 26 '11 at 07:36

What you want to do here is join the table on itself.

SELECT * FROM `table` AS `child` 
JOIN `table` AS `parent` 
ON `parent`.`next` = `child`.`id`

You need to give both copies of the table their own alias (here: parent and child) because otherwise you will run into uniqueness troubles.

score 0 · Answer 3 · answered Apr 06 '11 at 14:54

0

One way would be to create a loop that repeats a simple query.. I could post an example here. Are you using PHP?

answered Apr 06 '11 at 14:54

pragmatic

3 Answers3