MySQL

recursive query, category and parent_category

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jul 2008

Posts: 95

Reputation: jakesee is an unknown quantity at this point

Solved Threads: 4

jakesee

Offline

Junior Poster in Training

recursive query, category and parent_category

Jul 12th, 2008

Hi,

I have a table

`Categories` with fields (category_id, category, parent_category_id)

I want to write in a single query to replace the parent_category_id with the corresponding category_name.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
// OBJECTIVE: To return all category names AND parent category names
// if category IS root (no parent), parent category name IS empty string
 
SELECT x.*, y.category AS parent_category FROM AccountCategories x, AccountCategories y WHERE x.parent_category_id=y.category_id
 
// however, the above code only RETURNS sub categories,
// root categories with a parent_category_id MATCH IS NOT returned
// OBJECTIVE: To return all category names and parent category names
// if category is root (no parent), parent category name is empty string

SELECT x.*, y.category AS parent_category FROM AccountCategories x, AccountCategories y WHERE x.parent_category_id=y.category_id

// however, the above code only returns sub categories,
// root categories with a parent_category_id match is not returned

However, some parent_category_id is 0 for those categories that are top most. How can I write the query so that if parent_category_id=0, parent_category is empty string?

Thanks in advance.

Last edited by jakesee; Jul 12th, 2008 at 8:03 pm.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: recursive query, category and parent_category

Jul 12th, 2008

hi,

you may google Joe Celko trees

krs,
tesu

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.

•

Join Date: Jul 2008

Posts: 95

Reputation: jakesee is an unknown quantity at this point

Solved Threads: 4

jakesee

Offline

Junior Poster in Training

Re: recursive query, category and parent_category

Jul 13th, 2008

•

Originally Posted by tesuji

you may google Joe Celko trees

Thanks for the tip! Joe Celko trees present some interesting concepts that's quite new to me and probably a bit too much to digest at the moment.

Are you saying that with my current table (aka adjacency list, if i'm not wrong), I cannot achieve what I want in a single query call? And only with Joe Celko trees then it's possible?

However, from what I read and can so far understand, Joe Celko trees can only have 1 root. However, in my problem, there can be more than 1 root category. So how else can I approach this?

Last edited by jakesee; Jul 13th, 2008 at 2:18 pm.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: recursive query, category and parent_category

Jul 13th, 2008

Hi jakesee

For better handling you need one root only. This can easily be done by defining a master root where all other roots can be formally connected to. Only this master root will not have a parent. Additionally, in my tree table each node has a level number what simplifies traversing the tree.

There is a remarkable paper on

http://dev.mysql.com/tech-resources/...ical-data.html

which is based on Joe Celko's book: Trees and Hierarchies in SQL for Smarties.

The examples in that paper are mostly based on the nested set model, there is also a short introduction to the adjacency list model and its limitations. Your example is based on that adjacency list model.

I will think over how to traverse a complete tree only by one SQL statement (Actually, I am doing such traversing with C++ program by way of recursive functions because our category tree has some hundreds nodes with extremely various depths). Possibly the new WITH clause of SQL 2003 what has a recursive part may help here. I personally would prefer the nested set model but inserting new nodes in an already existing chain of nodes isn't that easy.

krs,
tesu

Last edited by tesuji; Jul 13th, 2008 at 6:07 pm.