Choose Promotional Price

Question

purpose

Get the lowest product price.

Problem

To illustrate my problem:

Line 1

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "Walmart"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "319.00"
• Product_State = 1 (suggested)

Line 2

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "Apple"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "0.00"
• Product_State = 0 (not suggested)

Line 3

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "BestBuy"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "299.00"
• Product_State = 1 (suggested)

Requesting the following topic ( What I have ) returns me zero as the best price for the problem described above, but the best price is 299.00 , at BestBuy , because zero in Product_Promotional_Price means that the product is not offered.

What i have

 SELECT MIN(LEAST(`Product_Original_Price`, `Product_Promotional_Price`)) as `minProductPrice` [...] 

More details

My request:

  SELECT `pr`.`Product_Id` as `productId`, `pr`.`Product_Name` as `productName`, ROUND(CAST(MIN(`map`.`Product_Original_Price`) AS DECIMAL)/100,2) as `minProductPrice`, `prm`.`Product_Measure_Name` as `measureName`, `prm`.`Product_Measure_Shortname` as `measureShortName`, `pri`.`Product_Thumbnail_Image_Url` as `thumbnailUrl`, `pr`.`Product_Markets_Quantity` as `numberOfMarketsThatHaveThisProduct` FROM `bm_market_products` as `map` JOIN `bm_products` as `pr` ON `map`.`Product_Id` = `pr`.`Product_Id` JOIN `bm_products_category_relationship` as `car` ON `pr`.`Product_Id` = `car`.`Product_Id` JOIN `bm_product_categories` as `ca` ON `car`.`Category_Id` = `ca`.`Category_Id` JOIN `bm_products_measure_relationship` as `prmr` ON `pr`.`Product_Id` = `prmr`.`Product_Id` JOIN `bm_product_measures` as `prm` ON `prmr`.`Measure_Id` = `prm`.`Product_Measure_Id` JOIN `bm_products_images` as `pri` ON `pr`.`Product_Id` = `pri`.`Product_Id` WHERE ("" IS NULL OR `map`.`Product_State` = 0) AND ("" IS NULL OR `ca`.`Category_Id` = 14) GROUP BY `map`.`Product_Id`; 

What the request returns:

What I already tried:

Given that Product_State determines whether a product is offered or not, follow this snippet:

 SELECT `pr`.`Product_Id` as `productId`, `pr`.`Product_Name` as `productName`, (IF(`map`.`Product_State` <> 0) THEN MIN(LEAST(`Product_Original_Price`, `Product_Promotional_Price`)) ELSE (`map`.Product_Original_Price) as `minProductPrice`, `prm`.`Product_Measure_Name` as `measureName`, `prm`.`Product_Measure_Shortname` as `measureShortName`, `pri`.`Product_Thumbnail_Image_Url` as `thumbnailUrl`, `pr`.`Product_Markets_Quantity` as `numberOfMarketsThatHaveThisProduct` [...] 

Can you see IF / THEN / ELSE ? This is what was added in relation to the previous request.

The above query does not work - the syntax is incorrect, I know, but it was just for illustration.

Decision

Gordon Linoff posted this answer and with this I did the following:

 SELECT [...] ROUND(CAST(MIN(CASE WHEN `map`.`Product_Promotional_Price` = 0 THEN `map`.`Product_Original_Price` ELSE LEAST(`map`.`Product_Promotional_Price`, `map`.`Product_Original_Price`) end) AS DECIMAL)/100,2) as `minProductPrice`, [...] 

To clarify, I just adapted its [Gordon Linoff] syntax to my scenario - ROUND to round numbers and CAST to set the value as a specific type.

Worked great !! Thanks!

Answer 1

You need to correct your logic to get the lowest price. The fact is that this is the best way. Here is an example:

 select MIN(case when `Product_Promotional_Price` = 0 then `Product_Original_Price` else least(`Product_Promotional_Price`, `Product_Original_Price`) end) 

Answer 2

put where Product_Original_Price!=0 and Product_Promotional_Price!=0 at the end;