Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

recursive select within mysql without application code?

Posted on 2004-04-23
6
Medium Priority
?
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
andreif earned 500 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

722 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