SQL Help - Advanced Query Tweak

[rotaour]

I have a store in the testing phase that has Easy Populate, Multi-Stores, QTPro, Credit Class/Gift Voucher and a couple lesser involved contributions installed. I seem to have everything working so far except a few minor things.

 

I just installed Order Editor v1.4 and am having an issue with a query thats not pulling everything I need.

 

To accomodate the multiple stores I created a category for each store, ie. Acme Apparel, Jacks Jerseys, etc, which is in turn filled with categories, ie. bags, headwear, tshirts, etc. The products then occupy these categories.

 

The problem Im having is that the main query in the Order Editor contrib only selects categories that are populated with products. This is because of a LEFT JOIN on the PRODUCTS_TO_CATEGORIES table. This makes a list that will contain several bags, headwear, and tshirts listings, one for each store/catgory in the pulldown that Order Editor uses. That makes it impossible to know which tshirt category I want to select to add a product to an order.

 

Here's the query;

$result = tep_db_query("SELECT products_name, p.products_id, x.categories_name, ptc.categories_id FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id=p.products_id LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ptc.products_id=p.products_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON cd.categories_id=ptc.categories_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " x ON x.categories_id=ptc.categories_id ORDER BY categories_id");

 

Any of you SQL Wizzes know how I can get this to include the main store categories so that when I open the pulldown each sub category is under its 'store' category?

 

Thanks