Recursive Select

Posted on 2008-11-15
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
    LVL 2

    Expert Comment

    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
    LVL 7

    Author Comment

    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.
    LVL 4

    Accepted Solution

    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.
    LVL 4

    Expert Comment

    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
    LVL 2

    Assisted Solution

    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
    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 =
    left join data level2 on level2.parent =


    have a look at :

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now