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 vote203 views1 answer
Postgresql comandos basicos
SQL
0 vote201 views1 answer
FILENAME /usr/bin/mysql does not exists. Make sure correct path is set in /etc/dump admin/settings.conf.
SQL
0 vote174 views1 answer
Ms Sql set us timezone
SQL
12 votes186 views10 answers
Google
SQL
5 votes188 views3 answers
Mysql check if null or empty string
SQL
6 votes194 views2 answers
Where clause in sql
SQL
0 vote211 views1 answer
SQL SERVER xquery count child nodes
SQL
1 vote202 views1 answer
Select tables with name like mysql
SQL
8 votes187 views2 answers
Phpmyadmin default password
SQL
8 votes193 views2 answers
Alert table name mysql
SQL