Is it possible to query a tree structure table in MySQL in a single query, to any depth?

mysql sql database-design data-structures hierarchical-data
Cameron Booth picture Cameron Booth · Oct 4, 2008 · Viewed 38.4k times · Source

I'm thinking the answer is no, but I'd love it it anybody had any insight into how to crawl a tree structure to any depth in SQL (MySQL), but with a single query

More specifically, given a tree structured table (id, data, data, parent_id), and one row in the table, is it possible to get all descendants (child/grandchild/etc), or for that matter all ancestors (parent/grandparent/etc) without knowing how far down or up it will go, using a single query?

Or is using some kind of recursion require, where I keep querying deeper until there are no new results?

Specifically, I'm using Ruby and Rails, but I'm guessing that's not very relevant.

Answer

Dave Cheney picture Dave Cheney · Oct 4, 2008

Yes, this is possible, it's a called a Modified Preorder Tree Traversal, as best described here

Joe Celko's Trees and Hierarchies in SQL for Smarties

A working example (in PHP) is provided here

http://www.sitepoint.com/article/hierarchical-data-database/2/

Related questions

Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+…

Read More
MySQL - how to front pad zip code with "0"?

In my MySQL InnoDB database, I have dirty zip code data that I want to clean up. The clean zip code data is when I have all 5 digits for a zip code (e.g. "90210"). But for some reason, I noticed …

Read More
How to create a multi-tenant database with shared table structures?

Our software currently runs on MySQL. The data of all tenants is stored in the same schema. Since we are using Ruby on Rails we can easily determine which data belongs to which tenant. However there are some companies of …

Read More