All products within MAIN category with cPath=xx_xx ?

[inveritas]

We have these categories that hold subcats (as most of us do), however, when a client selects a categoey, it would almost always only hold 1 or so item and the others are buried inside subcategories. We'd like to show all items in the MAIN category, so the client can narrow it down by selecting subcategories.

 

This is the current query, I lack knowledge to add to it, does anybody here have it already? Would be much appreciated!

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from ((" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

The table in which the main category data is held, is this:

 

TABLE_CATEGORIES . " c, "

 

field

 

parent_id =

 

So, cPath=73_82 would show only item# 82 from category 73, where as cPath=73 would show nothing. I'd like to show everything under the '73' umbrella.

 

 

Anybody?

[inveritas]

Please? Pretty please ;)