title: Return specific column case when
body: <h2>The goal</h2>
  
  <p>Return specific column if the product is an offer and has the lowest price.</p>
  
  <h2>The problem</h2>
  
  <p>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.</p>
  
  <p>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 <code>productState</code> column. I need suggestions (and, of course, the syntax, because I don't know [again]).</p>
  
  <h2>Details</h2>
  
  <p>I'm using C#.NET + MVC 4 + MySQL + Entity Framework 5 + Stored Procedure + Razor Engine.</p>
  
  <p>The follow fragment returns me the lowest price of a product list to comparison:</p>
  
  <pre><code>Min(Case When marketProducts.ProductPromotionalPrice = 0
      Then 
         marketProducts.ProductOriginalPrice 
      Else
         Least(
          marketProducts.ProductPromotionalPrice,
          marketProducts.ProductOriginalPrice
         )End) As minProductPrice,
  </code></pre>
  
  <p>But, I want to do something like this (of course — the syntax is wrong, but "works" to illustrate):</p>
  
  <pre><code>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,
  </code></pre>
  
  <p>The full query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This procedure returns me the following:</p>
  
  <p><img src="https://i.stack.imgur.com/OFdeD.jpg" alt="Image"></p>
  
  <p>Any ideas?</p>