Hierarchical data - MySQL all parent-child relationships

Questions

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.

Answers

http://en.wikipedia.org/wiki/Transitive_closure http://en.wikipedia.org/wiki/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:

http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 http://www.slideshare.net/billkarwin/models-for-hierarchical-data http://www.slideshare.net/billkarwin/models-for-hierarchical-data http://pragprog.com/book/bksqla/sql-antipatterns http://pragprog.com/book/bksqla/sql-antipatterns

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/14402568/mysql-all-parent-child-relationships

Related