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 vote137 views1 answer
Sql bild in function
SQL
1 vote124 views1 answer
MySQL AND Operator
SQL
-1 vote77 views2 answers
On delete set default sql server
SQL
4 votes173 views2 answers
Sql select except null
SQL
0 vote80 views1 answer
Now() -1 mysql
SQL
0 vote125 views2 answers
How to save result of sqlite query in a table
SQL
1 vote114 views1 answer
Nested if in mysql
SQL
8 votes131 views2 answers
Sqlsrv default port
SQL
0 vote130 views1 answer
How to close external sql connection in laravel
SQL
5 votes128 views2 answers
Oracle tables with column name
SQL