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

0 vote121 views1 answer
Psql check if sql script is valid
SQL
0 vote98 views4 answers
Mysql count table rows
SQL
0 vote101 views1 answer
How to print out column name differently in mysql
SQL
0 vote102 views1 answer
Sql show all users
SQL
2 votes151 views3 answers
Xampp mysql database not starting
SQL
12 votes108 views2 answers
Delete entries in postgresql
SQL
0 vote106 views1 answer
Restore backupfile discourse
SQL
1 vote110 views1 answer
What is in condition in sql
SQL
0 vote107 views2 answers
The metadata storage is not up to date, please run the sync-metadata-storage command to fix this issue.
SQL
0 vote122 views1 answer
Pgadmin save data greyed out
SQL