Nested CASE statements in MySQL

Question

My first time working with CASE Logic in SQL statements. Everything works if I remove the CASE statements, so the SQL is valid without it. I need to calculate the total item price based on a couple of things. If "Sales Price" is active AND "Option Upcharge" has a value, the total is: Qty * (Sales Price + Option Upcharge) If "Sales Price is inactive AND "Option Upcharge" has a value, the total is: Qty * (Price + Option Upcharge) If "Sales Price" is active AND "Option Upcharge" has NO value, the total is: Qty * Sales Price If "Sales Price is inactive AND "Option Upcharge" has NO value, the total is: Qty * Price If no Option was added, the value for tblproduct_options.option_upcharge is NULL in the output. Thanks for the help. Brett Here is my SQL: ``` SELECT tblshopping_cart.session_id, tblshopping_cart.product_id, tblshopping_cart.product_qty, tblshopping_cart.product_option, tblproducts.product_title, tblproducts.product_price, tblproducts.product_sale_price_status, tblproducts.produc…

Accepted Answer

You are missing a `THEN` in your first `CASE` Statement. (sorry I had to add table aliases)

```
SELECT sc.session_id
    , sc.product_id
    , sc.product_qty
    , sc.product_option
    , p.product_title
    , p.product_price
    , p.product_sale_price_status
    , p.product_sale_price
    , po.option_text
    , po.option_upcharge
    , CASE
        WHEN (p.product_sale_price_status = 'Y')
        THEN <-- add this
            CASE
            WHEN (po.option_upcharge IS NOT NULL)
                THEN (sc.product_qty * (p.product_sale_price + po.option_upcharge)) 
                ELSE (sc.product_qty * p.product_sale_price)    
            END
        ELSE
            CASE
            WHEN (po.option_upchage IS NOT NULL)
                THEN (sc.product_qty * (p.product_price + po.option_upcharge))
                ELSE (sc.product_qty * p.product_price)
            END
        END AS product_total
FROM tblshopping_cart sc
INNER JOIN tblproducts p
    ON sc.product_id = p.product_id
LEFT JOIN tblproduct_options po
    ON sc.product_option = po.option_product_id
ORDER BY sc.product_qty ASC
```

Nested CASE statements in MySQL

Solution

Problem