How to make a "distinct" join with MySQL

Question

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?

Answer 1

Use:

   SELECT p.upc,           p.name,           ph.price,           ph.date      FROM PRODUCT p LEFT JOIN PRICE_H ph ON ph.product_id = p.id      JOIN (SELECT a.product_id,                    MAX(a.date) AS max_date              FROM PRICE_H a          GROUP BY a.product_id) x ON x.product_id = ph.product_id                                  AND x.max_date = ph.date 

Answer 2

SELECT Product.UPC, Product.Name, Price_h.Price, Price_h.Date FROM Product LEFT JOIN Price_h ON (Product.Id = Price_h.Product_id AND Price_h.Date =    (SELECT MAX(Date) FROM Price_h ph1 WHERE ph1.Product_id = Product.Id));