How to make a "distinct" join with MySQL
65,946
Solution 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
Solution 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));
Solution 3
Try this:
SELECT Product.UPC, Product.Name, Price_h.Price, MAX(Price_h.Date)
FROM Product
INNER JOIN Price_h
ON Product.Id = Price_h.Product_id
GROUP BY Product.UPC, Product.Name, Price_h.Price
Author by
Steven Potter
Updated on September 24, 2020Comments
-
Steven Potter over 3 years
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?