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 vote311 views1 answer
Plsql find location of procedure
SQL
0 vote300 views1 answer
Order by in codeigniter query builder
SQL
1 vote307 views1 answer
Window function mysql
SQL
1 vote267 views2 answers
Procedure pl/sql
SQL
12 votes327 views6 answers
Distinct in sql server
SQL
2 votes318 views2 answers
Mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
SQL
1 vote298 views1 answer
Psql autocomplete for mac brew install
SQL
1 vote306 views1 answer
Declare table variable
SQL
5 votes295 views3 answers
Oracle list columns in schema
SQL
20 votes325 views1 answer
Get all table name in sql through php
SQL