Solved

recursive select within mysql without application code?

Posted on 2004-04-23
6
338 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:JDinBRLA
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
andreif earned 250 total points
ID: 10903674
You may consder using nested tree, check this article:

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=34896
0
 
LVL 15

Expert Comment

by:JakobA
ID: 10905033
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
 
LVL 15

Expert Comment

by:JakobA
ID: 10905081
Oops. i omitted a comma after  child_category. It should be there:
            b.category_title as child_category,
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:JDinBRLA
ID: 10922196
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
 
LVL 15

Expert Comment

by:JakobA
ID: 10922404
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
 
LVL 1

Author Comment

by:JDinBRLA
ID: 12784628
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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