[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Recursive Select

Posted on 2008-11-15
Medium Priority
Last Modified: 2012-05-05
Is it possible to do a "recursive select" in MySQL? I have a categories table with fields like this:

id INT4
parent_id INT4

So a category can have a parent category. Root categories have parent_id=NULL. So the table describes a tree of categories. Now let's say I have the ID of a sub-sub-category and I want to select the IDs of all parent categories (in this case two parent categories (the parent category and the parents parent category))... Is it possible to do it with ONE select statement? I can't use stored procedures/functions because it must run on MySQL 4. I know how to do it in PHP but my question is if it can be done in SQL (And how it is done if it's possible).
Question by:kayahr

Expert Comment

ID: 22968132
Sure, its called a SELF-JOIN and you can implement it using either JOINs or SubQueries.

I think the JOIN syntax is a little easier to read.  Here is what it might look like (assume that the sub-sub-category id is 1523 for this example):

SELECT  SubCategory.ParentID,
FROM     Category   AS SubSubCategory
JOIN       Category    AS SubCategory
   ON      SubSubCategory.ParentID = SubCategoryID
WHERE  SubSubCategory.ID = 1523

Author Comment

ID: 22968248
Ok, but this is not really recursive. It is hardcoded to display three category levels. It does not work for sub-sub-sub-categories and it doesn't work for simple sub-categories (which are directly below a root category). But it must work with any number of levels. So it must repeat checking for parent categories until parent_id is NULL.

Accepted Solution

obareey earned 672 total points
ID: 22968301
SELECT parent_id FROM table WHERE id='$id' OR
id=(SELECT parent_id FROM table WHERE id='$id') OR
id=(SELECT parent_id FROM table WHERE id=(SELECT parent_id FROM table WHERE id='$id')) OR

you can create an sql statement like this with using php easily. but the disadvantage is you must know how many steps you are down. if you develop a new method without requiring step number, please share.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.


Expert Comment

ID: 22968306
actually you can add a 'AND id<>NULL' and it should work.

SELECT parent_id FROM table WHERE id='$id' OR
id=(SELECT parent_id FROM table WHERE id='$id' AND id<>NULL) OR
id=(SELECT parent_id FROM table WHERE id=(SELECT parent_id FROM table WHERE id='$id'  AND id<>NULL)) OR

Assisted Solution

SQLShark earned 664 total points
ID: 22968613
Well, given your requirement, here is how I would approach it.

1. For a given ID, you need to calculate how many steps you are down into the sub-sub-sub categories.
    You could use a while loop for that.
2. Using another while loop, you could build a SQL statement to return the data set you want using Dynamic SQL.

Now, however, you are talking about a script instead of a simple statement.  So, the answer to your question is NO.  SQL does not have a recursive select.
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 664 total points
ID: 22970831
MySQL does not have a recursive query, SQl Server has since 2005 (called CTE query) and sql server 2008 even has a new hierarchy construct. So might have heard it from there.

To do it in MySQL, it is basicallt a matter of setting up and appropriate number of levels and left join on each of those "self" joins, using an alias for each subsequent join... e.g.

select *
from data level0
left join data level1 on level1.parent = level0.id
left join data level2 on level2.parent = level1.id


have a look at : http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question