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

2 votes148 views1 answer
Oracle list duplicates
SQL
0 vote155 views1 answer
Changing name of column and datatype in sql
SQL
0 vote185 views1 answer
Psql create usr
SQL
0 vote148 views1 answer
(PDOException(code: 2002): SQLSTATE[HY000] [2002] Permission denied at
SQL
0 vote130 views2 answers
Influxdb delete measurement based on date
SQL
0 vote152 views1 answer
Replace content value from old to new sql
SQL
0 vote109 views3 answers
Mysql calculate age from date of birth
SQL
0 vote175 views1 answer
Order child below the parent in mysqli
SQL
2 votes139 views1 answer
How to add where command in update comand with joins
SQL
0 vote143 views1 answer
Sql get count without group by
SQL