I have two MySQL tables (product and price history) that I would like to join:
Product
table:
Id = int
Name = varchar
Manufacturer = varchar
UPC = varchar
Date_added = datetime
Price_h
table:
Id = int
Product_id = int
Price = int
Date = datetime
I can perform a simple LEFT JOIN:
SELECT Product.UPC, Product.Name, Price_h.Price, Price_h.Date
FROM Product
LEFT JOIN Price_h
ON Product.Id = Price_h.Product_id;
But as expected if I have more than one entry for a product in the price history table, I get one result for each historical price.
How can a structure a join that will only return one instance of each produce with only the newest entry from the price history table joined to it?