get a recursive parent list

Here, I made a little function for you, I checked it in my database (MAMP) and it works fine

use mySchema;drop procedure if exists getParents;DELIMITER $$CREATE PROCEDURE getParents (in_ID int)BEGINDROP TEMPORARY TABLE IF EXISTS results;DROP TEMPORARY TABLE IF EXISTS temp2;DROP TEMPORARY TABLE IF EXISTS temp1;CREATE TEMPORARY TABLE temp1 AS  select distinct ID, parentID    from tasks    where parentID = in_ID;create TEMPORARY table results AS  Select ID, parentID from temp1;WHILE (select count(*) from temp1) DO  create TEMPORARY table temp2 as    select distinct ID, parentID       from tasks       where parentID in (select ID from temp1);  insert into results select ID, parentID from temp2;  drop TEMPORARY table if exists temp1;  create TEMPORARY table temp1 AS    select ID, parentID from temp2;  drop TEMPORARY table if exists temp2;END WHILE;select * from results;DROP TEMPORARY TABLE IF EXISTS results;DROP TEMPORARY TABLE IF EXISTS temp1;END $$DELIMITER ;

this code will return all parents to any depth.you can obviously add any additional fields to the results

use it like this

call getParents(9148)

for example

In this example we are checking 5 levels up:

select     t1.parentid, t2.parentid, t3.parentid, t4.parentid, t5.parentidfrom    tableName t1    left join tableName t2 on t1.parentid = t2.id    left join tableName t3 on t2.parentid = t3.id    left join tableName t4 on t3.parentid = t4.id    left join tableName t5 on t4.parentid = t5.idwhere    t1.name = 'thing3'