Recursive select in mysql

Question

I have a table with 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 want 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 having taskid =1 with rows having parenttaskid=1 with rows having parenttaskid in above selection & so on........

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

Answer 1

As Mike already said, MySQL does not support recursive select or recursive functions.

If you have a maximum logical limit on your task concatination (like 5) you can use hard coded self joins.

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

Which will produce this result: http://sqlfiddle.com/#!2/c9f80/1/0

By the way you have some self referencing tasks in your input data which would produce an infinity loop with recursion.

Answer 2

MySQL does not support recursive select.

But, the query below should do trick that 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