mysql - Left Join with condition from join -

suppose have 2 tables:

    table_1 has |product_id|max_date     table_2 has |invoice_id|product_id|sale_date|amount_sold 

table_2 stores sales products.

i want join tables in such way sum(amount_sold) table_2 grouped product_id considering sales occured before max_date each product table_1.

i tried

select *  (select * table_1)a  left join  (select product_id,sale_date,sum(amount_sold) table_2 group product_id)b on a.product_id=b.product_id , b.sale_date<a.max_date 

but didn't return correct sums.

i think answer might along these lines, have not been able figure out... mysql: how inner join table limit join 1 result highest vote or count?

any ideas? in advance.

you can include date criteria in join conditional, makes joins simpler read, imho.

select t2.product_id, sum(amount_sold)     table_1 t1 inner join table_2 t2     on t1.product_id = t2.product_id , t2.sale_date <= t1.max_date     group t2.product_id