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: http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — Kevin, Sep 27 '11 at 12:59
@Kevin I don't think it has anything related to the question. — Karolis, Sep 27 '11 at 13:05
@karolis - it's 'kinda' related, I get where he's pointing me..see EDIT above... — j-p, Sep 27 '11 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, Sep 27 '11 at 13:55

score 9 · Answer 1 · edited Dec 11 '12 at 09:39

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

This function looks more like it returns the children of an ID — htafoya, Sep 15 '17 at 23:58

score 7 · Accepted Answer · answered Sep 27 '11 at 12:59

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 '11 at 12:59

Karolis

9,396
29
38

1

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. – Louis Sep 27 '11 at 13:49
@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. – Karolis Sep 27 '11 at 14:08
@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. – j-p Sep 27 '11 at 18:51

get a recursive parent list

2 Answers2

Linked

Related