recursive query, category and parent_category
 

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.

// 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.

Re: recursive query, category and parent_category
 

hi,

you may google Joe Celko trees

krs,
tesu

Re: recursive query, category and parent_category
 

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?

Re: recursive query, category and parent_category
 

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

Re: recursive query, category and parent_category
 

thanks tesu,

All that infomation helped alot. I'll try to work from there on. Now, changing the structure will be quite a pain... =(