Need Help With SQL Statement

[warrenk]

I have two tables in a one to many relationship. There can be many ProdPrice records for each ProdMast record. What I am trying to do is find the lowest price for each product (key: ProdID) in the ProdPrice table and update the ProdMast table (LowPrice and LowCompanyID). What would the SQL statement be for this?

ProdMast:
ProdID
ProdName
ProdDescription
LowPrice
LowCompanyID

ProdPrice:
ProdID
CompanyID
Price

Thanks for any help!

 

[rac2]

What if two companies have the same price for a product?

 

[warrenk]

If the companies have the same price, I will just pick one of the companies for the lowest price.

Thanks for your help!
Warren

 

[PHV]

Why storing a derived/calculated value ?
Have a look here:

http://r937.com/relational.html

BTW, a query to retrieve the info:
SELECT M.ProdID, M.ProdName, M.ProdDescription, L.LowPrice, P.CompanyID
FROM ProdMast M INNER JOIN (
SELECT ProdID, MIN(Price) LowPrice FROM ProdPrice GROUP BY ProdID
) L ON M.ProdID = L.ProdID
INNER JOIN ProdPrice P ON L.ProdID = P.ProdID AND L.LowPrice = P.Price

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

Otherwise, assuming all companies have different prices for a product you could

UPDATE ProdMast SET
   LowPrice = LP.LowestPrice,
   LowCompanyID = PP.CompanyID
FROM ProdMast PM
JOIN ProdPrices P ON PP.ProdID = PM.ProdID
JOIN ( 
      SELECT ProdID, MIN(Price) AS LowestPrice
      FROM ProdPrices
      GROUP BY ProdID
     ) LP ON LP.ProdID = PM.ProdID
         AND LP.LowestPrice = PP.Price

 

[rac2]

Consider expanding your model to include a preference rating for vendors. Use lower values to indicate more preferred, and rank-order, that is, assign unique preference ratings to vendors. Then

UPDATE ProdMast SET
   LowPrice = LP.LowestPrice,
   LowCompanyID = PP.CompanyID
FROM ProdMast PM
JOIN ProdPrices P ON PP.ProdID = PM.ProdID
JOIN VendorRanks VR ON V.CompanyID = PP.CompanyID
JOIN (
      SELECT PP.ProdID, 
      MIN(PP.Price) AS LowestPrice,
      MIN(a.preference) AS Rank
      FROM ProdPrices PP
      JOIN VendorRanks a ON a.CompanyID = PP.CompanyID
      GROUP BY PP.ProdID
     ) LP ON LP.ProdID = PM.ProdID
         AND LP.LowestPrice = PP.Price
         AND LP.Rank = VR.preference

Or, get really fancy and define vendor preference ratings for every product! (This is left as an exercise for the reader.)

BTW, my previous query may fail at runtime if there happens to be duplicate prices because the assignment in the SET clause expects a single value.

Also the syntax may vary for different databases, I am using SQL Server.