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

5 votes208 views1 answer
Oracle nls parameters
SQL
0 vote184 views1 answer
Call object contructor and call methods of object
SQL
0 vote178 views1 answer
Get number of table colums in sql query
SQL
40 votes256 views6 answers
Update table mysql
SQL
4 votes180 views1 answer
Fetch first 10 rows in oracle sql developer
SQL
9 votes216 views3 answers
Sequence postgresql
SQL
0 vote234 views1 answer
Sql to excel pgadmin
SQL
0 vote184 views1 answer
Mysql streaming example
SQL
-1 vote133 views1 answer
How to create script to connect to oracle database and run query
SQL
0 vote193 views1 answer
Sql xampp gabungan nama awal dan akhir
SQL