mysql - JOIN a products table to price change table using job date -

i have jobs table looks this:

**jobs**   job_id | customer_id | date ------------------------------------ |    1  |           1 | 2012-01-03 | |    2  |           2 | 2013-02-04 | |    3  |           1 | 2013-03-05 | |    4  |           3 | 2013-05-04 | 

then have products table looks this:

**products**   product_id | description | price ----------------------------------- |         1 |      prod_1 | 25.50 |  |         2 |      prod_2 | 34.95 | 

and when prices changed have product_price_changes table this:

**product_price_changes**   price_change_id | product_id | price_change_date | old_price --------------------------------------------------------------- |              1 |          1 |        2013-01-01 |     20.00 | |              2 |          1 |        2013-02-05 |     23.00 | 

with unique index on (product_id,price_change_date)

i want create view grabs product pricing reflecting prices date job done.

which, data above, should create table this:

**view_job_pricing**   job_id | product_id | price ------------------------------ |     1 |          1 | 20.00 | |     1 |          2 | 34.95 | |     2 |          1 | 23.00 | |     2 |          2 | 34.95 | |     3 |          1 | 25.50 | |     3 |          2 | 34.95 | |     4 |          1 | 25.50 | |     4 |          2 | 34.95 | 

so should select product price change highest date, still less job date, if exists, otherwise should grab current product price.

i have works:

create view view_job_pricing  select j.job_id, p.product_id, max(price_change_date),         ifnull(ppc.old_price,p.price) price products p join jobs j left join product_price_changes ppc            on p.product_id = ppc.product_id , date < price_change_date group job_id, product_id; 

but pretty slow on real database (far more jobs , products). wondering if there better way. thanks!

try doing correlated subquery. allowed subqueries in select clause of view, , correlated subquery should use index on product.

create view view_job_pricing  select j.job_id, p.product_id, max(price_change_date),         coalesce((select ppc.price                  product_price_changes ppc                  p.product_id = ppc.product_id , j.date < ppc.price_change_date                  order ppc.price_change_date desc                  limit 1                 ),                 p.price) price products p cross join      jobs j 

you can simplify processing , improve performance changing structure of product_price_changes table. instead of having effective_date, have end_date. effdate , enddate columns, query faster , simpler.