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.

hi,

you may google Joe Celko trees

krs,
tesu

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?

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

commented: informative stuff +1

thanks tesu,

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

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
;

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.