recursive select within mysql without application code?

Hi all,

Let's say I have a table of the following structure:

CREATE TABLE `category` (
  `category_id` smallint(5) unsigned NOT NULL auto_increment,
  `category_parent` smallint(5) unsigned NOT NULL default '0',
  `category_title` varchar(150) NOT NULL default '',
.....
  PRIMARY KEY  (`category_id`)
) TYPE=MyISAM

that hold a list of categories

1, 0, 'Category A, the root'
2, 1, 'Category B, a child of A'
3, 1, 'Category C, a child of A'
4, 2, 'Category D, a child of B'
5, 2, 'Category E, a child of B'
6, 1, 'Category F, a child of A'
7, 6, 'Category G, a child of F'

etc...

I have programatically created a function that will find all the children of a parent, to a specified depth. Is there a way to do the same thing directly in MySQL in a single query?

ie: given a category id and an integer specifiying how many depth levels to traverse, i would like a list of all the category_id's that are children (or grand children, or grand grand children, etc...) up to a certain depth using mysql 4.0.13
LVL 1
JDinBRLAAsked:
Who is Participating?
 
andreifConnect With a Mentor Commented:
You may consder using nested tree, check this article:

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=34896
0
 
JakobACommented:
You can join your table to itself

SELECT a.category_title as ancestor_category,
            b.category_title as child_category
            c.category_title as grandchild_category
FROM   ( category AS a
              LEFT JOIN category AS b ON a.category_id = b.category_parent )
            LEFT JOIN category AS c ON b.category_id = c.category_parent
WHERE a.category_id = 1

Dont extend this too far though, it can get pretty slow if you try with 6-7 generation on a large tabel.

regards JakobA
0
 
JakobACommented:
Oops. i omitted a comma after  child_category. It should be there:
            b.category_title as child_category,
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JDinBRLAAuthor Commented:
JakobA> This solution will not work for me as I will be using the query on a table with 10k+ rows of variable length and need to get the selection up to an arbitrary depth (ie, usually greater than 6-7 generations)

andreif> that is an interesting way to create a tree stucture, and I will consider it in future applications but it will not work for this current situation without a terrible amount of trouble.

I'm not sure what I'm asking is possible with MySQL's current production version without application code.
0
 
JakobACommented:
If you set  (parentID,ID)  as primary key in you table I think you will find the cost surprisingly small as it will be a nearly pure index search as the table is joined again and again.
0
 
JDinBRLAAuthor Commented:
For the project I was working on, I ended up just reading the table into a tree structure in memory and performing operations on that structure. In the future though, I will probably use the nested set model in that article to do things like this.
0
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.

All Courses

From novice to tech pro — start learning today.