CODEWITHSUNDEEP
mysqljoin
user1840267
user1840267

Reputation: 428

mysql: Join product table with latest product prices

I have a table productPrice which contains more than one price for several products:

productPrice: 
id, 
unixTime, 
productId, 
price

I query the latest product prices and an average of all prices for this product in the last 24 hours:

SELECT 
(SELECT AVG(price) FROM productPrice WHERE productId =19 AND unixTime >= (unix_timestamp(NOW())-86400)) as avg, 
price,
unixTime  
FROM productPrice 
WHERE productId =19
ORDER BY unixTime DESC 
LIMIT 1

This returns the latest price, unixTime and the average price in a reasonable time (in my opinion - there may be a better way to do this). I have another table products, this is where I get the productId from:

products:
id (=productId in productPrice),
name,
url

I would like to select * from products and use the productId to join the result with the latest price and the average price, to get a result for all products like:

id,name,url,unixTime,price,avg

I read many similar questions here, but none seemed to work for me. Is there a good way to do this, or should I select the product first and do a single select for each productId after? Thank you in advance for any help!

EDIT: Included unixTime in the result to get more than one column from productPrice.

Upvotes: 0

Views: 524

Answers (2)

Allan Vital
Allan Vital

Reputation: 398

I couldn't test here, but I believe that this might work:

SELECT p.id, p.name, p.url, pp.price, (SELECT AVG(pr.price) FROM productPrice pr WHERE pr.productId = p.id AND pr.unixTime >= (unix_timestamp(NOW()) - 86400)) avg,
FROM productPrice pp INNER JOIN products p ON pp.productId = p.id
WHERE pp.productId = 19
ORDER BY pp.unixTime DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can accomplish what you want with correlated subqueries:

select p.*,
       (select avg(price) 
        from productPrice pp
        where pp.productId = p.productid and
              unixTime > (unix_timestamp(NOW()) - 86400)
      ) as avgprice,
       (select price
        from productPrice pp
        where pp.productId = p.productid 
        order by unixTime desc
        limit 1
      ) as mostrecentprice
from products p;

For performance, you want an index on productPrice(productid, unixtime, price).

Upvotes: 2

Related Questions