CODEWITHSUNDEEP
mysqlsql
Dave
Dave

Reputation: 41

SQL query: Current lowest price of products

I am struggeling with a SQL query for a project.

The following tables are given:

tblProduct => (proProductID, proProductName)
tblSeller => (selSellerID, selSellerName)
linkProductSeller => (linkID, linkProductID, linkSellerID, linkPrice, linkShippingPrice, linkDatetime)

Each time a price/shipping price of a product changes at a seller a new row in linkProductSeller is added.

I need to know the current lowest price/shipping price combination and seller for a product.

To get all current prices of a product I use this query

SELECT linkProductID, linkSellerID, linkPrice+linkShippingPrice as price, linkDatetime
FROM linkProductSeller AS a
WHERE linkDatetime = (
    SELECT MAX(linkDatetime)
    FROM linkProductSeller AS b
    WHERE a.linkProductID = b.linkProductID
AND a.linkSellerID = b.linkSellerID)
ORDER BY linkProductID ASC, price ASC, linkDatetime DESC

Thx for your support.

Upvotes: 0

Views: 350

Answers (2)

whatsupbros
whatsupbros

Reputation: 802

The answer above is not fully correct, I guess. If you need names of products and sellers, and it doesn't matter when precisely the prices were changed (you need just the current ones), then, I think, you'll need something like this:

select 
    p.proProductID, 
    p.proProductName, 
    s.selSellerID, 
    s.selSellerName, 
    min(l.linkPrice + l.linkShippingPrice) as Price
  from 
    tblProduct p,
    tblSeller s,
    (select * from linkProductSeller 
    where (linkProductID, linkSellerID, linkDateTime) 
    in 
        (select linkProductID, linkSellerID, max(linkDateTime) from linkProductSeller 
        group by linkProductID, linkSellerID 
        having linkDateTime = max(linkDateTime)) l
where 
    p.proProductID= l.linkProductID
    and s.selSellerID= m.linkSellerID
group by 
    p.proProductID, 
    p.proProductName, 
    s.selSellerID, 
    s.selSellerName

Upvotes: 0

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

All the data you need is in linkProductSeller so this should work

select l.linkProductId, min(linkPrice + linkShippingPrice) as minPrice
  from linkProductSeller l,
       (select linkProductID, linkSellerId, max(linkDateTime) as linkDateTime
          from linkProductSeller 
         group by linkProductID, linkSellerId) m
 where l.linkProductID = m.linkProductID
   and l.linkSellerID = m.linkSellerID
   and l.linkDateTime = m.linkDateTime
 group by linkProductId

Upvotes: 1

Related Questions