MySQL Self Join to traverse nested categories

[jggretton]

Hi all, this may have been asked many a time but I haven't had any luck finding it. If you know of a past post that's similar please do point me to it.

 

My problem is as follows:

 

I've got a table of categories, with an `itemID`, a `parentID` and a `title`.

 

The first level has a parentID of 0.

Categories are then nested eg. the children of an item with itemID=1 have a parentID of 1.

A categories can have multiple children, each child can have children etc etc.

 

I'd like to achieve a MySQL statement which given the itemID of the bottom most child, will return all the items in the parent category, all the items in the parent's parent category, .... , until we reach the base.

 

This may be unachievable so a more simple initial aim might be to get a statement which given the itemID of the bottom most child will return the parent item, the parent item's parent item, ... , until we reach the base.

 

Below is an example table:

 

CREATE TABLE `cats` (  `itemID` int(11) NOT NULL auto_increment,  `parentID` int(11) default NULL,  `title` varchar(128) collate utf8_unicode_ci default NULL,  PRIMARY KEY  (`itemID`)) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;insert  into `cats`(`itemID`,`parentID`,`title`) values (1,0,'Foods'),(2,0,'Drinks'),(3,1,'Sweets'),(4,1,'Savory'),(5,3,'Biscuits'),(6,3,'Cakes'),(7,6,'Jaffa'),(8,6,'Sponge'),(9,5,'Hobnob'),(10,5,'Custard cream'),(11,5,'Abbey crunch');

 

 

So far I've can pull out the parent categories with a table for each:

 

 

SELECT a.*FROM `cats` AS `a`, `cats` AS `b`, `cats` AS `c` WHERE c.itemID = 11 AND ( a.itemID = c.itemID OR a.itemID = c.parentID OR (a.itemID = b.parentID AND b.itemID = c.parentID) )GROUP BY a.itemID;

 

 

But this wont work for n depths

 

And I can also pull out pretty much everything by matching a.itemID = b.parentID

 

 

SELECT a.*FROM `cats` AS `a`, `cats` AS `b`WHERE a.itemID = b.parentID OR b.itemID = a.parentIDGROUP BY a.itemID

 

 

But this just gets me a mess of everything rather than the items I need.

 

Does anyone have any suggestions for how I might go about this and also about the efficiency of the query?

 

Thanks for any help you can provide.

 

James

 

 

[mikosiko]

this should help

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[mikosiko]

and I have this in my favorites  (in case that you could use some ideas)

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

[jggretton]

Thanks Mikosiko, they look very interesting - it's amazing how similar the first part of the dev.mysql.com article is to my question!

 

The second article is a little over my head at the moment but I might spend a few hours learning about MySQL functions and then perhaps I will be able to use it in my project.

 

Many thanks for your help,

 

James