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).
LVL 7
kayahrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQLSharkCommented:
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,
               SubCategory.ID,
               SubSubCategory.ID
FROM     Category   AS SubSubCategory
JOIN       Category    AS SubCategory
   ON      SubSubCategory.ParentID = SubCategoryID
WHERE  SubSubCategory.ID = 1523
0
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.
0
obareeyCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

obareeyCommented:
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
...
0
SQLSharkCommented:
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.
0
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

etc....


have a look at : http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.