get a recursive parent list

Using MySQL, I want to return a list of parents, from a table that has a field structure like this. ID,PARENTID,NAME (a standard parent-child hierarchy). I would like to traverse "up" the tree to return a list of ALL 'parents'.

I realize that "nested set", might be a better way to handle this - but currently I cannot change the structure of the data. I will look to do that in the future. Currently - my set of data will realistically contain a few levels of depth - nothing crazy... maybe 2-5 so my recursive hit shouldn't be 'too expensive'.

I've looked at the solutions presented in SQL Server get parent list - but this syntax bombs in mySQL...

Does anyone have an example of how to do this?

@kevin - thx for link - but I still get error. ("every derived ta开发者_StackOverflow社区ble must have it's own alias")

Here's what I did (modified syntax form above article - to 'fit' MySQL) - I clearly missed something...

SELECT parents.*
FROM  (
    SELECT taskID,  task,  parentID,  0 as level
    FROM   tasks
    WHERE taskidID = 9147
    UNION ALL
    SELECT  taskID, task,  parentID,  Level + 1 
    FROM   tasks
    WHERE  taskID = (SELECT parentID FROM parents ORDER BY level DESC LIMIT 1)
    )

thoughts???

EXAMPLE:

ID      PARENTID    NAME
9146    0       thing1
9147    0       thing2
9148    9146        thing3
9149    9148        thing4
9150    0       thing5
9151    9149        thing6

Query for parents of "thing3" Returns "9148,9146"

Query for parents of "thing6" Returns "9149,9148,9146,0"

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)
BEGIN
DROP 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.parentid
from
    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.id
where
    t1.name = 'thing3'