• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

Recursive Select

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).
3 Solutions
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
kayahrAuthor Commented:
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.
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.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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
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.
Mark WillsTopic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now