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

select t2.id,t2.parenttaskid from tbl_taskmaster t1 Inner join tbl_taskmaster t2 on t1.id=t2.parenttaskid where t1.parenttaskid=1 — Haris N I, Dec 10 '14 at 05:04
do you require an answer that is a sql statement, or would a stored procedure meet your needs? Is there a safe assumption about the max number of levels of recursion one would ever find? — RobP, Dec 10 '14 at 05:14
MySQL does not support recursive select is a short answer. but you should be able to get what you need using sub queries check out this post http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql — Jaylen, Dec 10 '14 at 05:16
I think you have a logic issue in your question. If taskid = 2 then task_id = 1 should be included in the result. the parent of task_id= 2 is 1 — Jaylen, Dec 10 '14 at 06:22

Jaylen · Answer 1 · 2014-12-10T06:42:44.300

1

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

edited Dec 10 '14 at 06:42

answered Dec 10 '14 at 05:30

Jaylen

39,043
40
128
221

i was tried your query bt its not getting the exact result http://sqlfiddle.com/#!2/9db0c3/6 – Haris N I Dec 10 '14 at 06:02
I just updated the answer. I think you have a logic issue in your question. If the taskid = 2 then the output should also be 1 2 3 4 5 the parent of task_id = 2 is 1 so 1 should be included http://sqlfiddle.com/#!2/9db0c3/15 – Jaylen Dec 10 '14 at 06:18
if taskid=2, then it means i want to select rows having taskid =2 & also the rows having parenttaskid =2 & if any child tasks having parenttaskid from above resultset. i hope u got the logic... – Haris N I Dec 10 '14 at 06:23
Actually my requirement is to delete tasks related to given taskid, and further child tasks having parenttaskid as given taskid. – Haris N I Dec 10 '14 at 06:27
@Haris you can try to set cascade delete on foreign key if your requirement is for delete only – Sandeep Dec 10 '14 at 06:33
2

I suggest you take my query and play around with it. :) – Jaylen Dec 10 '14 at 06:34

score 1 · Answer 2 · answered Dec 10 '14 at 07:01

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.

Recursive select in mysql

2 Answers2