Return specific column case when

Question

The goal

Return specific column if the product is an offer and has the lowest price.

The problem

I'm creating an application to products price comparison. I'm already getting the products list and the lowest price of a specific product. But, in a day the product can be set to be an offer and have the lowest price of all other products. When that happens, I must to identify to the user such information — with a flag or something like this.

I'm thinking to do this behavior using the query itself — but I do not know, sometimes it seems that this practice is inconsistent because if the product is not on sale, do not know how I'll handle this in C# that will be waiting for the productState column. I need suggestions (and, of course, the syntax, because I don't know [again]).

Details

I'm using C#.NET + MVC 4 + MySQL + Entity Framework 5 + Stored Procedure + Razor Engine.

The follow fragment returns me the lowest price of a product list to comparison:

Min(Case When marketProducts.ProductPromotionalPrice = 0
    Then 
       marketProducts.ProductOriginalPrice 
    Else
       Least(
        marketProducts.ProductPromotionalPrice,
        marketProducts.ProductOriginalPrice
       )End) As minProductPrice,

But, I want to do something like this (of course — the syntax is wrong, but "works" to illustrate):

Min(Case When marketProducts.ProductPromotionalPrice = 0
    Then 
       marketProducts.ProductOriginalPrice 
    Else
       marketProducts.ProductState = 1 As productState, /* 
                                                           This is a normal
                                                           column.
                                                           1 means that 
                                                           the product is on 
                                                           offer.
                                                        */
       Least(
        marketProducts.ProductPromotionalPrice,
        marketProducts.ProductOriginalPrice
       )End) As minProductPrice,

The full query:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getProductsList`(IN `categoryId` INT, IN `productState` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    Select product.Id As productId,
        product.Name As productName,
        Min(Case When marketProducts.ProductPromotionalPrice = 0
            Then marketProducts.ProductOriginalPrice Else
            Least(marketProducts.ProductPromotionalPrice, marketProducts.ProductOriginalPrice)
            End) As minProductPrice,
            measure.Name As measureName,
            measure.Abbreviation As measureAbbreviation,
            productsImages.ProductImageThumbnail As thumbnailUrl,
            product.InMarketsQuantity as numberOfMarketsThatHaveThisProduct
    From bm_market_products as marketProducts
    Join bm_products As product On marketProducts.ProductId = product.Id
    Join bm_categories As category On product.CategoryId = category.Id
    Join bm_measures As measure On product.MeasureId = measure.Id
    Join bm_products_images As productsImages On product.Id = productsImages.ProductId
    WHERE (productState Is Null Or marketProducts.ProductState = productState)
    And (categoryId Is Null Or category.Id = categoryId)
    Group By marketProducts.ProductId;
END

This procedure returns me the following:

Any ideas?

Answer 1

The reason why your syntax is wrong is you are stuffing two columns in the space of one, sometimes. The solution is to always have both columns as shown below:

Select product.Id As productId,
    product.Name As productName,
    Min(Case When marketProducts.ProductPromotionalPrice = 0
        Then marketProducts.ProductOriginalPrice Else
        Least(marketProducts.ProductPromotionalPrice, marketProducts.ProductOriginalPrice)
        End) As minProductPrice,
    Min(Case When marketProducts.ProductPromotionalPrice = 0
            and marketProducts.ProductPromotionalPrice < marketProducts.ProductOriginalPrice
        Then 0 Else 1
        End) As productState,
        measure.Name As measureName,
        measure.Abbreviation As measureAbbreviation,
        productsImages.ProductImageThumbnail As thumbnailUrl,
        product.InMarketsQuantity as numberOfMarketsThatHaveThisProduct
From bm_market_products as marketProducts
Join bm_products As product On marketProducts.ProductId = product.Id
Join bm_categories As category On product.CategoryId = category.Id
Join bm_measures As measure On product.MeasureId = measure.Id
Join bm_products_images As productsImages On product.Id = productsImages.ProductId
WHERE (productState Is Null Or marketProducts.ProductState = productState)
And (categoryId Is Null Or category.Id = categoryId)
Group By marketProducts.ProductId;