Ad:

MySQL Discussion Thread
Marked Solved
Views: 7777

Jul 12th, 2008

recursive query, category and parent_category

Expand Post »

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.

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.

jakesee

Reputation Points: 21

Solved Threads: 5

Junior Poster

Offline

130 posts
since Jul 2008

Permalink

Jul 12th, 2008

Re: recursive query, category and parent_category

hi,

you may google Joe Celko trees

krs,
tesu

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

Jul 13th, 2008

Re: recursive query, category and parent_category

Click to Expand / Collapse Quote 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.

jakesee

Reputation Points: 21

Solved Threads: 5

Junior Poster

Offline

130 posts
since Jul 2008

Permalink

Jul 13th, 2008

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

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

Jul 14th, 2008

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... =(

jakesee

Reputation Points: 21

Solved Threads: 5

Junior Poster

Offline

130 posts
since Jul 2008

Permalink

Nov 5th, 2010

Re: recursive query, category and parent_category

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 !):

MySQL Syntax (Toggle Plain Text)
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
;
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...

MySQL Syntax (Toggle Plain Text)
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
;
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
;

Last edited by OVOVO; Nov 5th, 2010 at 3:45 am.

OVOVO

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Nov 2010

Permalink

Nov 5th, 2010

Re: recursive query, category and parent_category

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

jakesee

Reputation Points: 21

Solved Threads: 5

Junior Poster

Offline

130 posts
since Jul 2008

Permalink

Nov 5th, 2010

Re: recursive query, category and parent_category

Click to Expand / Collapse Quote originally posted by 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

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

OVOVO

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Nov 2010

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Would someone please try & modify this query in a way that...

Next Thread in MySQL Forum Timeline: Working Database template / sample?

DaniWeb Message

Cancel Changes

User Name
Password