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

16 votes274 views3 answers
Jwt secret key generator laravel
SQL
1 vote276 views4 answers
Psql: error: FATAL: database "odoo" does not exist
SQL
6 votes268 views1 answer
Postgres get size of database
SQL
0 vote249 views1 answer
Sql server update top n records
SQL
0 vote260 views1 answer
Dnsmasq.conf location macos
SQL
57 votes455 views5 answers
How to create a new user in postgresql
SQL
0 vote307 views1 answer
Delete from table where length sql
SQL
2 votes268 views1 answer
Drop all database tables oracle sql developer
SQL
0 vote263 views1 answer
R dbConnect(odbc::odbc() to ms sql server remote
SQL
1 vote281 views1 answer
Postgres set sequence value to max id
SQL