MySQL - Recursing a tree structure

mysql recursion hierarchy
Extrakun picture Extrakun · Mar 4, 2010 · Viewed 35.7k times · Source

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?

Answer

Pekka picture Pekka · Mar 4, 2010

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

Related questions

Getting hierarchy data from self-referencing tables

Let's say you have the following table: items(item_id, item_parent) ... and it is a self-referencing table - item_parent refers to item_id. What SQL query would you use to SELECT all items in the table along with …

Read More
Recursive stored functions in MySQL

I'm trying to make a function that recursively builds a path for a specific category CREATE FUNCTION getPath(inId INT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE return_path TEXT; DECLARE return_parent_id INT; SELECT CONCAT('/', name) INTO return_…

Read More
MySQL does not support recursive functions? why? since when?

I've written a stored FUNCTION that calls itself, recursively. However when I run it in a query I get this shameless error: Error: 1424 SQLSTATE: HY000 (ER_SP_NO_RECURSION) Message: Recursive stored functions and triggers are not allowed. "Not allowed"? …

Read More