Do not return the smallest value if

Question

goal

Do not return the lowest price whose markets are suspended.

Problem

I do not know the syntax.

Scenario

To obtain the lowest and highest prices for a particular product, the following stored procedure exists:

BEGIN Select Min(Case When product.PromotionalPrice = 0 Then product.OriginalPrice Else Least(product.PromotionalPrice, product.OriginalPrice) End) As minProductPrice, Max(Case When product.PromotionalPrice = 0 Then product.OriginalPrice Else Least(product.PromotionalPrice, product.OriginalPrice) End) As maxProductPrice From products As product Where product.Name = 'Playstation 3'; END 

Context: there are markets and products . Products belong to the markets. If any market is suspended, it does not display its products and does not add them to the max / min price comparison.

Do you understand everything? I want to exclude products whose markets are suspended from the Min or Max statement of the above request.

Tables

Here is the markets table:

 +----+------+-------------+ | Id | Name | SituationId | +----+------+-------------+ | 1 | A | 1 | +----+------+-------------+ | 2 | B | 2 | +----+------+-------------+ | 3 | C | 3 | +----+------+-------------+ 

Here is the markets_situations table:

 +----+-----------+ | Id | Name | +----+-----------+ | 1 | Neutral | +----+-----------+ | 2 | Premium | +----+-----------+ | 3 | Suspended | +----+-----------+ 

And finally, here is the products table:

 +----+---------------+--------+------------------+---------------+ | Id | Name | Market | PromotionalPrice | OriginalPrice | +----+---------------+--------+------------------+---------------+ | 1 | Xbox 360 | 1 | 0 | 225,00 | +----+---------------+--------+------------------+---------------+ | 2 | Xbox 360 | 2 | 99,00 | 175,00 | +----+---------------+--------+------------------+---------------+ | 3 | Xbox 360 | 3 | 0 | 135,00 | +----+---------------+--------+------------------+---------------+ | 4 | Playstation 3 | 1 | 0 | 189,00 | +----+---------------+--------+------------------+---------------+ | 5 | Playstation 3 | 2 | 125,00 | 165,00 | +----+---------------+--------+------------------+---------------+ | 6 | Playstation 3 | 3 | 110,00 | 185,00 | +----+---------------+--------+------------------+---------------+ 

To improve understanding

I do not want to display 110,00 as the Min price for the result of the stored procedure, because its market ( C ) is Suspended .

What i have already done

I have already tried the following, but to no avail:

 BEGIN [...] Where product.Name = 'Playstation 3' And marketSituation.Id <> 3; END 

What's happening? The And condition does nothing. The request continues to return the price of the suspended market to me.

Answer 1

 Select Min(Case When product.PromotionalPrice = 0 Then product.OriginalPrice Else Least(product.PromotionalPrice, product.OriginalPrice) End) As minProductPrice, Max(Case When product.PromotionalPrice = 0 Then product.OriginalPrice Else Least(product.PromotionalPrice, product.OriginalPrice) End) As maxProductPrice From products As product Inner join markets on product.market = markets.id AND markets.SituationId <> 3 Where product.Name = 'Playstation 3'; 

Answer 2

How about something like

 Select Min(Case When product.PromotionalPrice = 0 Then product.OriginalPrice Else Least(product.PromotionalPrice, product.OriginalPrice) End) As minProductPrice, Max(Case When product.PromotionalPrice = 0 Then product.OriginalPrice Else Least(product.PromotionalPrice, product.OriginalPrice) End) As maxProductPrice From products As product INNER JOIN Markets ON Product.Market = Markets.Id Where product.Name = 'Playstation 3' AND Markets.SituationID <> 3