Simplify my queries

[subzerostudio]

Hi,

 

I have a query which finds the lowest product price in all subcategories of a category (this is using oscommerce)..  The way I am doing it at the moment is to first have a query to find all subcategory ids, then loop through each of these with another query to find the lowest product price in that category.  This seems a little laborious - as if there are 20 subcategories in a category - it involves running 21 queries on the database!  There must be a way to do this in one query, but I can't work out how. Any advice / help much apprecaited. Code is below.

 

Thanks

 

Mike

 

		//get all subcategories from this category
	$sub_category_query_raw = "SELECT c.categories_id " . 
							  "FROM categories c " .
							  "WHERE c.parent_id=" . $categories['categories_id'];
	$sub_category_query = mysql_query($sub_category_query_raw);

	$lowest_price = 99999; 
	//now loop through each category and find the product with the lowest price
	while ($subcat = mysql_fetch_array($sub_category_query)) {
		$lowest_price_in_cat_query_raw = "SELECT MIN(products_price) as min_price  " . 
						   	       "FROM products p, products_to_categories p2c " .
						   	       "WHERE p2c.products_id=p.products_id " . 
						   	       "AND p2c.categories_id = " . $subcat['categories_id'];
		$lowest_price_in_cat_query = tep_db_query($lowest_price_in_cat_query_raw);
		$lowest_price_in_cat_array = tep_db_fetch_array($lowest_price_in_cat_query);

		if ($lowest_price_in_cat_array['min_price'] < $lowest_price)
			$lowest_price = $lowest_price_in_cat_array['min_price'];		
	}

[skali]

SELECT MIN(products_price) as min_pric
FROM products p, products_to_categories p2c
WHERE p2c.products_id=p.products_id 
AND p2c.categories_id IN (SELECT c.categories_id 
                          FROM categories c
                          WHERE c.parent_id=" . $categories['categories_id']
                          )