MySQL - Recursing a tree structure

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,开发者_如何学Go no matter how deep, just using MySQL?

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).

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.

Since mysql statements can return only table-structured data, how do you imagine the returned tree-structure?

It is possible to do a selection with [parent_id, child_id] table, but it requires temporary table and I've seen it done on DB2, not on MySQL.

Check this article for implementation on tree-like structures stored in MySQL: http://articles.sitepoint.com/article/hierarchical-data-database/