CODEWITHSUNDEEP
mysqlcase
ILikeTacos
ILikeTacos

Reputation: 18676

MySQL Case is Only Evaluating First Condition.

I have two tables, one table contains payment cycles, and the other contains things that need to be paid depending annually, monthly, etc.

I'm querying the database this way:

SELECT
    L.UserID,
    U.Username,
    L.CostPerCycle,
    CT.CycleType,
    CASE L.CycleTypeID
        WHEN L.CycleTypeID = 1 THEN L.CostPerCycle * 12
        WHEN L.CycleTypeID = 2 THEN L.CostPerCycle * 3
        WHEN L.CycleTypeID = 3 THEN L.CostPerCycle * 2
        WHEN L.CycleTypeID = 4 THEN L.CostPerCycle
        WHEN L.CycleTypeID = 5 THEN L.CostPerCycle
    END AS AnnualCost
FROM
    Links L
    LEFT JOIN Users U ON L.UserID = U.UserID
    LEFT JOIN CycleTypes CT ON L.CycleTypeID = CT.CycleTypeID
WHERE
    L.CycleTypeID <> 6;

The problem is that MySQL is returning NULL values instead of the result of the multiplication, and I'm not exactly sure why.

I've noticed that only the first condition is being evaluated, but I'm not sure what's going on with the others.

In other words:

If there's a record with a CycleTypeID = 1 then MySQL multiplies CostPerCycle times 12. However if the CycleTypeID is different than the first condition, then MySQL does something and I'm not sure what it does that basically 'ignores' the rest of my conditions.

Hope that's enough information to point me to the right direction!

Thanks!

Upvotes: 0

Views: 227

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is the case statement you want:

CASE 
    WHEN L.CycleTypeID = 1 THEN L.CostPerCycle * 12
    WHEN L.CycleTypeID = 2 THEN L.CostPerCycle * 3
    WHEN L.CycleTypeID = 3 THEN L.CostPerCycle * 2
    WHEN L.CycleTypeID = 4 THEN L.CostPerCycle
    WHEN L.CycleTypeID = 5 THEN L.CostPerCycle
END AS AnnualCost;

You are mixing two different forms of the statement. You could also phrase this as:

CASE L.CycleTypeID
    WHEN 1 THEN L.CostPerCycle * 12
    WHEN 2 THEN L.CostPerCycle * 3
    WHEN 3 THEN L.CostPerCycle * 2
    WHEN 4 THEN L.CostPerCycle
    WHEN 5 THEN L.CostPerCycle
END AS AnnualCost;

The reason your version returns NULL values is because it is comparing L.CycleTypeId to the value of the expression L.CycleTypeId = x. This latter expression is either 0 or 1. The only things that could possibly match are ids 0 and 1. Everything else will fall through. Without an else clause, these get NULL values.

Upvotes: 3

Related Questions