How to join tables and select by max date

Question

I have this tables, one have the products and the other have the price of the product but it change by date, so I want to have one select that have product and the last price.

I made this select but it returns all the products and all prices And I just want all products with the last price inserted

SELECT tbl_product.*, tbl_product_price.price FROM tbl_product JOIN tbl_product_price ON tbl_product.id = tbl_product_price.FK_id_product

tbl_product
id, product
 1   bread
 2   soda
 3   milk

tbl_product_price
FK_id_product, price,   last_date_change
     1          0.11   2020-09-15 17:04:41
     1          0.12   2020-09-16 09:13:53
     1          0.13   2020-09-17 12:20:25
     2          0.65   2020-09-15 20:00:07
     2          0.69   2020-09-16 11:33:40
     3          0.91   2020-09-15 02:54:32
     3          1.00   2020-09-16 13:33:22
     3          0.95   2020-09-17 15:41:11

my expeded query result

id, product,  price
 1   bread    0.13
 2   soda     0.69
 3   milk     0.95

I hope I explained fine Thanks

Answer 1

A correlated subquery might come handy for this:

select 
    p.*,
    (
        select pp.price 
        from tbl_product_price pp 
        where pp.fk_id_product = p.id 
        order by pp.last_date_change desc limit 1
    ) as price
from tbl_product p

For performance with this query, consider an index on tbl_product_price(fk_id_product, last_date_change desc, price).