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;