Recursive Select.... :(

[drbigfresh]

I have a table with the following design:

 

ID | ParentID | CategoryName

 

The Data would be like this:

1 | null | Gaming

2 | 1 | PC Games

3 | 1 | XBox 360 Games

4 | null | Health

5 | 4 | Vitamins

 

At this point there won't ever be more than 2 levels, and I have my select like this (this is going in a drop down menu):

select  d_categories.id, concat(a.catname,'  > ', d_categories.catname ) as catname from d_categories inner join d_categories a on d_categories.parentid = a.id where a.parentid is null

 

Which would return this:

2 | Gaming > PC Games

3 | Gaming > XBox 360 Games

5 | Health > Vitamins

 

But what I want it to return is this:

1 | Gaming

2 | Gaming > PC Games

3 | Gaming > XBox 360 Games

4 | Health

5 | Health > Vitamins

 

Now I have searched high and low, and tried just about everything to make it work, but I can't figure out how to do the join on the table so that it even comes out close to this..and after 48 hours I am debating just doing it as 2 selects which seems messy. Does anyone have any help they can idea  on this one? I know recursion is not fun, but since it's limited to 2 levels at this point I'm hoping someone has a query kicking around somewhere. ???

 

 

[Barand]

see

 

http://www.phpfreaks.com/forums/index.php/topic,163263.msg715900.html#msg715900