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

17 votes142 views4 answers
Show tables sql server
SQL
7 votes167 views1 answer
Sql pagination oracle
SQL
0 vote145 views1 answer
SQLite3::SQLException: table "categories" already exists: CREATE TABLE "categories" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL
SQL
0 vote150 views1 answer
Sql server select value large text
SQL
0 vote157 views1 answer
Oracle db timestamp auto add
SQL
1 vote199 views1 answer
Insert pg result of select
SQL
1 vote142 views1 answer
Select statement to print longest name
SQL
0 vote153 views2 answers
Mysql get routine parameter list
SQL
2 votes152 views1 answer
How to check if a row is null in sql
SQL
0 vote154 views1 answer
Flush user resource mysql
SQL