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

4 votes269 views2 answers
Sql table
SQL
0 vote286 views1 answer
Raiserror with nowait
SQL
-1 vote266 views1 answer
Insert results of a stored procedure into a temporary table
SQL
0 vote298 views1 answer
T-sql delete view if exists
SQL
0 vote284 views1 answer
Search mysql database for column
SQL
4 votes264 views1 answer
Restart for pg_hba.conf file
SQL
0 vote267 views1 answer
Mysql_error replacement
SQL
0 vote297 views1 answer
SQL Server Splitting a string column into multiple rows, while repeating ID column
SQL
1 vote355 views1 answer
Sql and
SQL
2 votes272 views2 answers
How to DROP a table in mysql
SQL