CODEWITHSUNDEEP
mysql
lolbas
lolbas

Reputation: 794

Select next row based on current row field within one query

Let's say the table contains a list of waypoints.

 id | nextID
----+--------
  1 |      2 
  2 |      4
  3 |      5
  4 |      1
  5 |      0

Based on this table we have two pathes

How do I query the table to get these results:

      When starting ID = 1
 id | nextID
----+--------
  1 |      2 
  2 |      4
  4 |      1

      When starting ID = 3
 id | nextID
----+--------
  3 |      5
  5 |      0

Upvotes: 1

Views: 58

Answers (2)

mirmdasif
mirmdasif

Reputation: 6354

What you are trying to achieve is called hierarchical query.

A hierarchical query is operated on Hierarchical Data where parent-child relationship is stored in the same table.

Oracle, DB2, Postgres have special syntaxes to query on hierarchical data.

In Oracle if you want to find the all possible path from a start point you have to use START WITH CONNECT BY query.

Query

SELECT id "start_point", CONNECT_BY_ISCYCLE "Cycle",
  LEVEL, SYS_CONNECT_BY_PATH(id, '/') "Path"
FROM waypoints START WITH id = 1
CONNECT BY NOCYCLE PRIOR next_id = id AND LEVEL <= 4;

OUTPUT

enter image description here

MySQL 8 will support this feature in future. But you can implement your own by following this blog post.

Hope this helps you. Ping me if you need further clarifications.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

For the linear problem, you can do this. Note that I've used NULL instead of 0 for orphans...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT PRIMARY KEY
,nextID INT NULL
);

INSERT INTO my_table VALUES
(1,2),
(2,4),
(3,5),
(4,1),
(5,NULL);

SELECT CONCAT_WS(',',a.id,b.id,c.id) path 
  FROM my_table a 
  LEFT 
  JOIN my_table b 
    ON b.nextID = a.id 
  LEFT 
  JOIN my_table c 
    ON c.nextID = b.id 
 WHERE a.nextID IS NULL;
+------+
| path |
+------+
| 5,3  |
+------+

Upvotes: 1

Related Questions