recursive select within mysql without application code?
Posted on 2004-04-23
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`)
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'
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