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 vote426 views1 answer
Python get backup of sql
SQL
0 vote349 views1 answer
Except in sql
SQL
0 vote355 views1 answer
Connect to nginx server mysql remotly
SQL
1 vote363 views2 answers
Group_concat limit mysql
SQL
2 votes321 views1 answer
Mysql auto increment jumping
SQL
0 vote335 views4 answers
Sql year
SQL
4 votes391 views1 answer
Sql delete multiple conditions
SQL
3 votes359 views1 answer
How to calculate number of days between two dates excluding weekends in oracle
SQL
0 vote374 views1 answer
Error: com.mysql.cj.jdbc.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
SQL
-2 votes339 views2 answers
Sqlserver docker
SQL