I have a table named table. It has a field named id with type INT(11) that stands for an identifier of the row, it has other fields but I don't think they are relevant for this problem.
I have another table named table_children. It has a field named parent with type INT(11) that refers to table.id as a foreign key. It has another field named child with type INT(11) that also refers to table.id as a foreign key. This table describes table row to table row parent-child relationships.
Here is a probable setting.
table table_children
id parent child
0 0 1
1 1 2
2 1 3
3 3 4
4
How can I get the id's of all the descendents of 0 in a minimum number of requests? The answer here would be 1, 2, 3, 4.
Thank you for your help.
解决方案
With MySQL, the easiest way I do this is to store all paths in the tree, creating a transitive closure.
table_children
parent child
0 0
1 1
2 2
3 3
4 4
0 1
0 2
0 3
0 4
1 2
1 3
1 4
3 4
Now you can query it thus:
SELECT t.*
FROM table_children c
JOIN table t ON c.child = t.id
WHERE c.parent = 0;
See also: