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
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With