I've got a webshop with a tree of categories stored as a nested set tree (lft, rgt and lvl attributes for each category).
What I want to do is show all categories which contains a certain type of product as a full tree. I can easily get Ids of all categories in question either as a separate SQL or with a join. The problem is; I get only the deep child category, and what I really need is the full path of that category.
Example (SELECT * FROM categories ORDER BY lft):
- Music
- LP
- CD
- Movies
- VHS
- DVD
- Books
- Paperback
- Hardcover
Let's say I want to show categories with new material and that happens only to be DVD and LP:
(SELECT * FROM categories WHERE new > 0 (OR IS PARENT OF SUCH) ORDER BY lft):
- Music
- LP
- Movies
- DVD
Hope someone is able to help, thanks in advance!
(Just out of curiosity: is it possible to accumulate a number upwards? Eg. the "new" counter? So that music.new would always be bigger or equal to LP.new)
