Select Promotional Price

Question

The goal

Get the lowest price of a product.

The problem

To illustrate my problem:

Row 1

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

Row 2

• Product_Id = 1
• Product_Name = "iPhone 5"
• Market_Name = "Apple"
• Product_Original_Price = "359.00"
• Product_Promotional_Price = "0.00"
• Product_State = 0 (isn't on offer)

Row 3

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

The query of the next topic (What I have) is returning me zero as the best price of the problem illustrated above — but the best price is 299.00, by BestBuy, because zero at Product_Promotional_Price means that the product isn't on offer.

What I have

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

Details

My query:

    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 query returns:

What I already have tried:

Considering that Product_State determines whether a product is on offer or not, follow this fragment:

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 the IF/THEN/ELSE? This is what has been added in relation to the previous query.

The above query doesn't work — syntax isn't correct, I know, but it was just to illustrate.

The solution

Gordon Linoff posted this answer and with it, I made this:

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 his [Gordon Linoff] syntax to my scenario — with ROUND to rounding numbers and CAST to set a value as a certain type.

Worked perfectly!! Thanks!!

Answer 1

You need to fix your logic for getting the lowest price. A case statement 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)