Recursive selection in mysql

Question

I have a table with the following format

TaskID ParentTaskID 1 1 2 1 3 2 4 2 5 2 10 10 11 11 12 11 13 0 14 14 

I need the result as below if taskid is 1

 TaskID 1 2 3 4 5 

if its 2 then

 Taskid 2 3 4 5 

if its 10 then 10

means I want to select rows that have taskid = 1, with rows that have parenttaskid = 1, with rows that have parenttaskid in the above selection, etc.

use this script http://sqlfiddle.com/#!2/9db0c3/6

Answer 1

MySQL does not support recursive selection.

But the request below should do the trick you are looking for

 SELECT t.TaskID FROM task AS t INNER JOIN ( SELECT DISTINCT a.TaskID FROM task AS a INNER JOIN ( SELECT TaskID FROM task WHERE TaskID = 11 OR ParentTaskID = 11 UNION ALL SELECT ParentTaskID FROM task WHERE TaskID = 11 OR ParentTaskID = 11 ) AS s ON s.TaskID = a.ParentTaskID ) AS s ON s.TaskID = t.TaskID 

Answer 2

As Mike said, MySQL does not support recursive selection or recursion functions.

If you have a maximum logical limit for your task concatenation (e.g. 5), you can use hard-coded self-connections.

 SELECT t1.taskid as taskid1, t2.taskid as taskid2, t3.taskid as taskid3, t4.taskid as taskid4, t5.taskid as taskid5 FROM task t1 LEFT JOIN task t2 ON t2.parenttaskid = t1.taskid LEFT JOIN task t3 ON t3.parenttaskid = t2.taskid LEFT JOIN task t4 ON t4.parenttaskid = t3.taskid LEFT JOIN task t5 ON t5.parenttaskid = t4.taskid 

What will give this result: http://sqlfiddle.com/#!2/c9f80/1/0

By the way, you have some self-regulatory tasks in your input that will create an infinity loop with recursion.