mysql - SQL Subquery between two tables, the max date to compare with another date -

i have 2 tables:

first

order   product     date of order 4772    cf007115    2014-03-31 14:24:29.000 

and second

product     date of buy             price  cf007115    2014-03-18              111.398 cf007115    2014-03-27              103.121 cf007115    2014-05-08              0.061 cf007115    2014-07-21              0.062 cf007115    2015-01-22              0.065 cf007115    2015-05-29              0.068 

i need next result

order   product     date of order           date of buy             price 4772    cf007115    2014-03-31              2014-03-27              103,121 

the result must show price near order.

i trying this:

select distinct  dbo.opsinvalor.orden, dbo.opsinvalor.codcomponente, dbo.opsinvalor.fecha_declaracion, dbo.entradasparaop3.fechaingstock, dbo.entradasparaop3.ppp  dbo.opsinvalor  left outer join dbo.entradasparaop3 on dbo.opsinvalor.codcomponente = dbo.entradasparaop3.articulo     (dbo.opsinvalor.codcomponente = 'cf007115') group dbo.opsinvalor.orden, dbo.opsinvalor.codcomponente, dbo.opsinvalor.fecha_declaracion, dbo.entradasparaop3.fechaingstock,                        dbo.entradasparaop3.ppp having      (dbo.opsinvalor.fecha_declaracion >= max(dbo.entradasparaop3.fechaingstock)) 

this result

4772    cf007115    2014-03-31  2014-03-18  111,398 4772    cf007115    2014-03-31  2014-03-27  103,121 

i try subqueries result give me records of second table

i expect first table orders , second (with more rows) product table - have name convention.

you can try this:

select * orders o inner join (     select *     products p     p.[date of buy] <= o.[date of order]     order p[date of buy] desc limit 1 ) p