get a recursive parent list

Question

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 table 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"

See this similar question: stackoverflow.com/questions/1382573/… — Kevin
– Kevin, Commented Sep 27, 2011 at 12:59
@Kevin I don't think it has anything related to the question. — Karolis
– Karolis, Commented Sep 27, 2011 at 13:05
@karolis - it's 'kinda' related, I get where he's pointing me..see EDIT above... — j-p
– j-p, Commented Sep 27, 2011 at 13:37
@jpmyob so add an alias for that table, for instance as my_alias. But in any case it's not possible to rewrite SQL server's query for MySQL because MySQL does not support recursive subquerying. If you don't want to limit the query up to several levels then you need to create a stored function or procedure. — Karolis
– Karolis, Commented Sep 27, 2011 at 13:55

j0k · Accepted Answer · 2012-12-11 09:39:23Z

9

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

edited Dec 11, 2012 at 9:39

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Dec 11, 2012 at 9:21

UV.

4926 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

j0k Over a year ago

Instead of <pre><code> you can use 4 spaces to highlight your code.

htafoya Over a year ago

This function looks more like it returns the children of an ID

Karolis · Accepted Answer · 2011-09-27 12:59:02Z

7

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'

answered Sep 27, 2011 at 12:59

Karolis

9,59231 silver badges41 bronze badges

3 Comments

Louis Over a year ago

After reading the edit in the question, the OP is trying to work on a recursive SQL (two selects joined by a UNION ALL), not on a fixed five-level tree like yours.

Karolis Over a year ago

@Louis Yeah, but MySQL does not support recursive queries. Also, OP says: my set of data will realistically contain a few levels of depth. So this can be some kind of solution, because it works with up to 5 levels. Another way would be to write a stored function / procedure.

j-p Over a year ago

@louis - not exactly, i offered the union because it came from the "other post" dealing with this issue of a string of parents... regardless - it doesn't work... and this might - I'll have to try it in context. THX.

Collectives™ on Stack Overflow

get a recursive parent list

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related