Hi!

I'm using a nested set in a MySQL table to describe a hierarchy of categories, and an additional table describing products.

Category table;

id
name
left
right

Products table;

id
categoryId
name

How can I retrieve the full path, containing all parent categories, of a product? I.e.:

RootCategory > SubCategory 1 > SubCategory 2 > ... > SubCategory n > Product

Say for example that I want to list all products from SubCategory1 and it's sub categories, and with each given Product I want the full tree path to that product - is this possible?

This is as far as I've got - but the structure is not quite right...

select
 parent.`name` as name,
 parent.`id` as id,
 group_concat(parent.`name` separator '/') as path
from
 categories as node,
 categories as parent,
 (select
  inode.`id` as id,
  inode.`name` as name
 from
  categories as inode,
  categories as iparent
 where
  inode.`lft` between iparent.`lft` and iparent.`rgt`
  and
  iparent.`id`=4 /* The category from which to list products */
 order by
  inode.`lft`) as sub
where
 node.`lft` between parent.`lft` and parent.`rgt`
 and
 node.`id`=sub.`id`
group by
 sub.`id`
order by
 node.`lft`