Query all descendants of a person in a mysql family table

We often place some tree structures in a table, such as genealogy and menus. The biggest problem with this type of table is that when we need to query all the sub-records of a certain record, it is difficult to use a simple SQL statement. We can write a process to do this. Below is an example I wrote. FghgfhgfhgfMySQLCREATETABLE 'People' ('id'

We often place some tree structures in a table, such as genealogy and menus. The biggest problem with this type of table is that when we need to query all the sub-records of a certain record, it is difficult to use a simple SQL statement. We can write a process to do this. Below is an example I wrote. Fghgfhgfhgf MySQL create table 'People' ('id'

We often place some tree structures in a table, such as genealogy and menus. The biggest problem with this type of table is that when we need to query all the sub-records of a certain record, it is difficult to use a simple SQL statement. We can write a process to do this. Below is an example I wrote. Fghgfhgfhgf MySQL

Create table 'People' ('id' INT (11) not null, 'name' VARCHAR (50) null default null, 'pid' INT (11) not null default '0', primary key ('id'); create definer = 'root' @ '%' PROCEDURE 'getchildren '(IN 'parentid' INT) language SQL not deterministic contains SQL SECURITY DEFINER COMMENT 'get all future generations of someone 'BEGIN # TEMPORARY TABLE for storing results DROP TABLE IF EXISTS children; CREATE TEMPORARY TABLE children SELECT 0 pLevel, p. * FROM 'people' p WHERE id = parentId; # temporary table that stores intermediate results drop table if exists tem; create temporary table tem SELECT id FROM 'others' limit 0; # Step-by-step filling of the descendant SET @ pLevel = 1; REPEAT # truncate table tem; # INSERT the descendant id of the current level INTO the temporary table insert into tem SELECT p. id FROM 'others' P, children c WHERE p. pid = c. id AND c. pLevel = (@ pLevel-1); # INSERT the child data of the current level INTO the result temporary table insert into children SELECT @ pLevel, p. * FROM 'others' p, tem t WHERE p. id = t. id; SET @ pLevel = @ pLevel + 1; until not exists (SELECT * FROM tem) OR @ pLevel> 10 end repeat; # adjust the table structure, DELETE temporary columns and unwanted data alter table children drop column pLevel; delete from children WHERE id = parentId; # Return result SELECT * from children; END