Solved

recursive select within mysql without application code?

Posted on 2004-04-23
6
343 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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