php - mysql getting max min values across multiple tables -

i want price range products 2 tables.

table1 (products):

pid | products_name | products_model 1   | product 1.....| model 1 2   | product 2.....| model 2 

table2 (products_prices):

pid | nerchant_id   | price | sale_price | sale_start_date | sale_expire_date 1   | rvtbv         | 11.90 | 0          | null            | null 1   | qnunu         | 11.90 | 9.90       | 2013-05-01      | 2013-12-31 1   | yolor         | 12.90 | 10.90      | 2013-04-01      | 2013-12-31 2   | rvtbv         | 20.90 | 0          | null            | null 2   | qnunu         | 29.90 | 25.90      | 2013-04-01      | 2013-12-31 2   | yolor         | 29.90 | 0          | null            | null 

how result price range this:

pid | products_name | products_model | min_price | max_price 1   | product 1.... | model 1 ...... | 10.90 ... | 12.90 2   | product 2.... | model 2 ...... | 20.90 ... | 29.90 

i using main query products data table1 loop php foreach product min max values depending on sale start , expiry dates.

it work don't subqueries php. prefer 1 mysql query performance reasons.

thanks helping.

until following statement best

select      p.pid,                  p.manufacturers_id,                  p.products_image,          p.products_name,                  (select min(if(current_date between pp.sale_start_date , pp.sale_expire_date , pp.sale_price>'0', pp.sale_price, pp.price)) products_prices pp p.pid = pid) min_price,                  (select max(if(current_date between pp.sale_start_date , pp.sale_expire_date , pp.products_sale_price>'0', pp.sale_price, pp.price)) products_prices pp p.pid = pp.pid) max_price                         products p                         p.products_status = '1'                         , p.categories_id = '1'                         order min_price asc  limit 0, 100 

is possible optimize little bit?

resumé:

sometimes solution simple don´t see it;)

ok project price comparison plattform. products updated hourly or that, not prices change. let´s 10% updated. data must retrieverd each visit of website.

in case more reads writes (80-20).

i can add 2 columns products table (min_price , max_price) update once if price_data changes.

on 1 hand update little bit more complicated that´s not drama. on other hand data retrieved fast.

i have testet 3 options based on 15000 products retrieve 100 rows: worst: group approch (over 1 sec) good: approach of arheops (0,12 sec) best: update once 2 colums (0,07 sec)

i go third option.

thanks anyway!

that depend of query.

if query values product, optimal:

select pid,products_name,products_model,    (select min(price) price price.pid=product.pid) min_price,    (select max(price) price price.pid=product.pid) max_price  product  some_filter_here; 

if need got full table, 1 best:

select a.pid,products_name,products_model,min_price,max_price       product   left join (     select pid,min(price) min_price, max(price) max_price          price group pid            ) b on b.pid=a.pid