MySQL - Recursing a tree structure

Question

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?

See stackoverflow.com/questions/587488/…

a'r
– a'r

2010-03-04 11:09:02 +00:00
Commented Mar 4, 2010 at 11:09 — a'r
– a'r, Commented Mar 4, 2010 at 11:09

Pekka · Accepted Answer · 2011-09-22 17:22:01Z

29

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

edited Sep 22, 2011 at 17:22

answered Mar 4, 2010 at 11:07

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pekka Over a year ago

@Ed the way back machine still has it. Updated.

Robbie Over a year ago

Moving on two years, there's a non-wayback machined version of that article at mikehillyer.com/articles/managing-hierarchical-data-in-mysql as well. (Basically it's moved to the author's own blog)

GWed Over a year ago

This is why answers should always contain full details of an answer with a reference to the source, not the reference being the answer

Jack O'Connor Over a year ago

It looks like adding new nodes to a nested sets table require modifying potentially the entire table though.

James John McGuire 'Jahmic' Over a year ago

Could you summarize what the article stated?

deltragon · Accepted Answer · 2020-03-04 14:16:23Z

11

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.

answered Mar 4, 2020 at 14:16

deltragon

5735 silver badges17 bronze badges

Comments

Eimantas · Accepted Answer · 2010-03-04 11:06:38Z

-1

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/

answered Mar 4, 2010 at 11:06

Eimantas

49.5k17 gold badges134 silver badges173 bronze badges

Collectives™ on Stack Overflow

MySQL - Recursing a tree structure

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related