•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 391,994 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,283 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 534 | Replies: 4 | Solved
 |
•
•
Join Date: Jul 2008
Posts: 33
Reputation: 
Rep Power: 1
Solved Threads: 1
Light Poster
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.
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.
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.
Last edited by jakesee : Jul 12th, 2008 at 7:03 pm.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
hi,
you may google Joe Celko trees
krs,
tesu
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: 33
Reputation:
Rep Power: 1
Solved Threads: 1
Light Poster
•
•
•
•
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 1:18 pm.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
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
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 5:07 pm.
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.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
Other Threads in the MySQL Forum
- Previous Thread: One-To-Many Relationship (adding data)
- Next Thread: SHA1 encription
