CODEWITHSUNDEEP
mysql
Toma Tomov
Toma Tomov

Reputation: 1654

How to order result by the variable from the CASE statement

How to order results by the variable promotion_price which is from the CASE statement. What I get as error is:

Unknown column 'promotion_price' in 'where clause'

And this is my query:

SELECT product.*,
    CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END promotion_price
FROM product LEFT JOIN page ON product.category_id=page.id
LEFT JOIN promotion_product ON product.id=promotion_product.main_product_id
LEFT JOIN promotion ON promotion_product.promo_id=promotion.id 
WHERE (page.id = 12 OR page.id_in = 12) 
AND promotion_price >= 49.50 
AND promotion_price <= 108.89
GROUP BY product.id
ORDER BY  promotion_price ASC

Thank you!

Upvotes: 0

Views: 22

Answers (2)

Vivek
Vivek

Reputation: 803

As we know, we can't use the inline column in where clause in single SQL, you can try the below one with subquery.

SELECT * 
FROM
    (SELECT 
         CASE
            WHEN promotion.type = 1 
               THEN product.price - (product.price * promotion.value/100)
            WHEN promotion.type = 2 
               THEN product.price - promotion.value
            ELSE product.price
        END promotion_price,
        product.*
    FROM
        product 
    LEFT JOIN 
        page ON product.category_id = page.id
    LEFT JOIN 
        promotion_product ON product.id = promotion_product.main_product_id
    LEFT JOIN 
        promotion ON promotion_product.promo_id = promotion.id 
    WHERE 
        (page.id = 12 OR page.id_in = 12)
    GROUP BY 
        product.id) s
WHERE
    promotion_price >= 49.50 
    AND promotion_price <= 108.89
ORDER BY  
    promotion_price ASC

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can't use a column alias in where condition you must repeat the code 

SELECT product.*,
    CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END promotion_price
FROM product LEFT JOIN page ON product.category_id=page.id
LEFT JOIN promotion_product ON product.id=promotion_product.main_product_id
LEFT JOIN promotion ON promotion_product.promo_id=promotion.id 
WHERE (page.id = 12 OR page.id_in = 12) 
AND (CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END ) >= 49.50 
AND (CASE
        WHEN promotion.type=1 THEN product.price - (product.price * promotion.value/100)
        WHEN promotion.type=2 THEN product.price - promotion.value
        ELSE product.price
    END) <= 108.89
GROUP BY product.id
ORDER BY  promotion_price ASC

the where condition is evaluated before the evaluation of the select column alias so in the moment of evaluation of the where condition the select column alias are not know to query engine

Upvotes: 1

Related Questions