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

7 votes212 views3 answers
Python and mysql connectivity
SQL
1 vote200 views1 answer
Setval pgsql
SQL
6 votes232 views1 answer
T-sql test if table exists
SQL
0 vote227 views4 answers
What is unique key in sql
SQL
0 vote216 views1 answer
Intellij idea add mysql connector
SQL
1 vote202 views1 answer
Sql update table remove spaces
SQL
5 votes236 views2 answers
Mysql dump mysql db cli
SQL
2 votes269 views1 answer
Select a certain number of entries from select mysql
SQL
23 votes220 views6 answers
Create table sql server
SQL
11 votes205 views2 answers
Insert current date sql
SQL