get a recursive parent list

by

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

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. Create a Cumulative Sum Column in MySQL
  3. Equivalent of explode() to work with strings in MySQL
  4. How can I tell when a MySQL table was last updated?
  5. mysql select dynamic row values as column names, another column as value
  6. Operand Should Contain 1 Column – MySQL NOT IN
  7. MySQL DELETE FROM with subquery as condition
  8. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  9. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  10. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  11. Simple Random Samples from a Sql database
  12. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  13. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  14. How do I use properly CASE..WHEN in MySQL
  15. Determine Rank based on Multiple Columns in MySQL
  16. Checking multiple columns for one value
  17. What is better in MYSQL count(*) or count(1)?
  18. Delete query not working in mysql
  19. How to get the count of each distinct value in a column? [duplicate]
  20. How to enable INNODB in mysql
  21. Bash script to insert values in MySQL
  22. MySQL GROUP BY DateTime +/- 3 seconds
  23. How do I lag columns in MySQL?
  24. How to get multiple records against one record based on relation?
  25. SQL query to check if a name begins and ends with a vowel
  26. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  27. How to use a user variables in MySQL LIKE clause?
  28. MySql – How get value in previous row and value in next row? [duplicate]
  29. Updating table in trigger after update on the same table
  30. Syntax error on MERGE statement

Leave a Comment