MySQL - Recursing a tree structure
Solution 1
There's a good-looking article over at mysql.com outlining various ways of managing hierarchical data. I think it provides a full solution to your question, and shows various less simple, but faster approaches (e.g. Nested Sets).
Solution 2
This is an old question, but since i stumbled over this searching for a solution.
Since MySQL 8.0, you can use a recursive CTE for this:
WITH RECURSIVE tmp (id) AS
(
SELECT id
FROM locations
WHERE parent_id IS NULL
UNION ALL
SELECT l.id
FROM tmp AS p JOIN locations AS l
ON p.id = l.parent_id
)
SELECT * FROM tmp
ORDER BY id;
This assumes a slightly different DB structure than in the original question (eg. there is only one table containing both parents/children), however, I am sure that this technique applies there as well.
Extrakun
A struggling game developer, learning Unity3D, Flash Actionscript 3, C++ with QT 4.5 and proficient in PHP.
Updated on March 05, 2020Comments
-
Extrakun about 4 years
I have a database table which link locations together; a location can be in a location, which can be inside another location.
location (<id>, ....) location_parent (<location_id>, <parent_id>)
Here's the MySQL/PHP to go down for a depth of one:
$sql = "SELECT id FROM se_locations_services WHERE parent_locationid IN ( SELECT location_id FROM se_locations_parent WHERE parent_id = '$locationid' )";
How do I, given a parent location, gets all its descendants locations, no matter how deep, just using MySQL?