Joining products and categories in a combined MPTT/adjacency model

[Brad Holland]

I have a legacy app that uses an indefinite number of category/subcategories as an edge list, aka an adjacency model for products on an ecommerce site. We are introducing the Modified Preorder Tree Traversal (MPTT) model by adding the lft and rgt columns to the categories table while maintaining the adjacency data as well.

Following a few tutorials, I have the MPTT data in place. This article was a great help:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

and this code for the conversion:

http://articles.sitepoint.com/article/hierarchical-data-database/3

One of the reasons for going to this model is to allow navigation of the various categories without having to know the category depth ahead of time. Navigating the categories beforehand was easy, but some categories ultimately do not contain products. We had no way of determining whether a category (or any of its descendant categories) would actually contain a product, so customers might traverse a series of categories and subcategories and eventually get a page that says "Sorry. There are no products in this category."

Now, the MPTT model should allow us to determine this information beforehand and not show the category if there are no products, but I'm drawing a blank on how to join the products on the categories to figure this out.

Basically, the current category id is given and the formula is this:
"Get all subcategories that have at least one active product that has a category id that references a category that has a lft value between the current category's lft and rgt values."

I know this is going to turn out to be a simple join of some sort, but I'm drawing a blank. Would appreciate any help.

Thanks,

Brad