Mysql get nested records

DROP PROCEDURE IF EXISTS famsubtree;
DELIMITER go
CREATE PROCEDURE famsubtree( root INT )
BEGIN
  DROP TABLE IF EXISTS famsubtree;
  CREATE TABLE famsubtree
    SELECT childID, parentID, 0 AS level
    FROM familytree
    WHERE parentID = root;
  ALTER TABLE famsubtree ADD PRIMARY KEY(childID,parentID);
  REPEAT
    INSERT IGNORE INTO famsubtree
      SELECT f.childID, f.parentID, s.level+1
      FROM familytree AS f
      JOIN famsubtree AS s ON f.parentID = s.childID;
  UNTIL Row_Count() = 0 END REPEAT;
END;
go
DELIMITER;

And use to query:

call famsubtree(1);       -- from the root you can see forever
SELECT Concat(Space(level),parentID) AS Parent, Group_Concat(childID ORDER BY childID) AS Child
FROM famsubtree
GROUP BY parentID;

Similar codes

2 votes98 views1 answer
How to check grants on a package in oracle
SQL
1 vote93 views1 answer
Mysql connect command
SQL
5 votes105 views1 answer
Ignore duplicate rows in sqlite
SQL
2 votes106 views1 answer
11:04:35 PM [mysql] Error: MySQL shutdown unexpectedly. 11:04:35 PM [mysql] This may be due to a blocked port, missing dependencies,
SQL
0 vote94 views1 answer
In mysql workbench contnent not feching
SQL
1 vote105 views1 answer
Import database from a file using mysql commandline
SQL
57 votes262 views5 answers
How to create a new user in postgresql
SQL
13 votes103 views2 answers
Get tables in database sql
SQL
0 vote100 views2 answers
Oracle create chain rule
SQL
0 vote107 views1 answer
OFFSET consulta mysql
SQL