Solved

recursive select within mysql without application code?

Posted on 2004-04-23
6
342 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

823 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