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 vote410 views1 answer
Mysql workbench format date
SQL
5 votes378 views1 answer
Ora-00054 resource busy and acquire with nowait
SQL
0 vote340 views1 answer
Postgres float to int
SQL
0 vote355 views1 answer
How to get data between a last week in mysql
SQL
1 vote374 views1 answer
Sql select max value from multiple rows
SQL
0 vote335 views1 answer
Mysql max number not returning correct value
SQL
-1 vote377 views2 answers
Installing mysql on centos 7
SQL
0 vote343 views1 answer
Truncate table sql server foreign key
SQL
1 vote350 views1 answer
Mysql random limit 1
SQL
1 vote320 views1 answer
Enable mysql slow query log
SQL