32

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?

Smar
  • 8,109
  • 3
  • 36
  • 48
Steven Potter
  • 619
  • 1
  • 8
  • 13

5 Answers5

30

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I understand why you say it should be faster, but I have yet to find a circumstance where it is. I think it may have something to do with the large number of records I have in the price history table (3.5M). Perhaps it would help if I added an index to the Price_h.date column? – Steven Potter Jun 21 '10 at 23:27
  • 2
    It seems that this query takes approximately the same amount of time no matter how many results it returns whereas a1ex07's query takes longer with increased results, as would be expected. I was just a little surprised at how slow this is for small result sets and how fast a1ex07's solution is for the same small results set. Since in the "Real world" I will be working with rather large result sets I have selected this as the answer. – Steven Potter Jun 23 '10 at 04:38
  • You saved my day :), Thanks – Eyad Farra Apr 10 '17 at 08:08
5
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));
a1ex07
  • 36,826
  • 12
  • 90
  • 103
4

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
Justin Ethier
  • 131,333
  • 52
  • 229
  • 284
  • 1
    That'd work but it wouldnt give him the correct price history entry that corresponds with the max(date)... – Ariel Jun 21 '10 at 21:49
  • This query returns Max(date) for each (Product.UPC+Product.Name+Price_h.Price). It is not the latest price for the product. – a1ex07 Jun 21 '10 at 21:52
  • 1
    Ariel and a1ex07 are correct. This query does manage to return the correct date, but not the correct price. – Steven Potter Jun 21 '10 at 22:01
0
SELECT n.product_id, 
       n.product_name,
       n.product_articul,
       n.product_price,
       n.product_discount,
       n.product_description, 
       n.product_care,
       (SELECT photo_name FROM siamm_product_photos WHERE product_id = n.product_id LIMIT 1) AS photo_name
FROM siamm_product as n;
Dirty-flow
  • 2,306
  • 11
  • 30
  • 49
vlnik
  • 1
0

Why not keep it simple and fast:

SELECT 
 Product.UPC, Product.Name, Price_h.Price, Price_h.Date
FROM 
 Product
LEFT JOIN 
 Price_h
 ON Product.Id = Price_h.Product_id;
ORDER BY
 Price_h.Date DESC
LIMIT 1
Henry
  • 1