MySQL nested set hierarchy with foreign table
- by Björn
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`
