recursive query, category and parent_category

[jakesee]

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.

[tesuji]

hi,

you may google Joe Celko trees

krs,
tesu

[jakesee]

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?

[tesuji]

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/articles/hierarchical-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

[jakesee]

thanks tesu,

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

[OVOVO]

After more than 2 years, I think that this can be usefull...

My table categories have the parents ids of the roots categories with the value NULL

Two solutions:

#1. Don't show the parent's name of the roots (NULL !):

SELECT c.category_id, c.category AS 'Name', c1.category AS 'Parent Name'
FROM categories AS c, categories AS c1
WHERE c.parent_category_id = c1.category_id
;

#2. Show the parent's name of the roots, for those that love subqueries...

SELECT c.category_id AS catId, c.category AS 'Name',
  (SELECT category
   FROM categories
   WHERE category_id = c.parent_category_id 
  ) AS 'Parent Name'
FROM categories AS c
;

[jakesee]

Hi OVOVO,

This post is really ancient! I can only vaguely remember what this post was for... I love how my post end up linking to some complicated research papers.

Thanks for helping though. Might come in handy in the future. =)

Jake

[OVOVO]

Hi OVOVO,

This post is really ancient! I can only vaguely remember what this post was for... I love how my post end up linking to some complicated research papers.

Thanks for helping though. Might come in handy in the future. =)

Jake

Hi Jake,

I don´t like complicated and obscure solutions.

I think that we have to search for simple solutions allways, then I posted this.

You are wellcome...

OVOVO