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 vote164 views1 answer
Opensuse status MySQL
SQL
0 vote159 views1 answer
Distinct data types in a table sql query
SQL
0 vote151 views1 answer
Insert to first table if field A equals field B from a second table using sql
SQL
3 votes170 views3 answers
Postgresql random number
SQL
0 vote167 views3 answers
ChAnge TABLE name apex oracle
SQL
0 vote298 views1 answer
Oracle sql alter view add column
SQL
0 vote161 views1 answer
Sql join on a subquery
SQL
0 vote165 views1 answer
Postgres enumerated type
SQL
0 vote176 views1 answer
Mysql replication change database name
SQL
0 vote173 views1 answer
Access denied for user 'root'@'localhost' (using password no) stackoverflow
SQL