MySQL CASE based on previous CASE value

Question

In MySQL, is it possible to have two CASE statements in the SELECT clause, where the second CASE statement relies on the first CASE statement?

For example, consider the following query:

SELECT CASE WHEN `user`.`id` < 500 THEN 'awesome' ELSE 'lame' END
    AS `status`

     ,  CASE WHEN `status` = 'awesome' THEN 'You rock' ELSE 'You stink' END
    AS `message`

  FROM `user`

Basically, the user ID determines the status, and then the status determines the message.

However, as you might have guessed, this query generates this error:

Unknown column 'status'

The only solution I have found so far is two generate a temporary table, view, or subquery, and then the message is determined by the status returned in this subquery.

Is there a way to write this query without the use of a temporary table, view or subquery? I'm trying to avoid these constructs to keep the query simple and optimized if possible. Thank you!

I don't think you can do it without at least one subquery. And I don't think it will cause any performance problems :) — Rodrigo Assis, Jul 03 '14 at 21:29
@RodrigoAssis That's exaclty what I said when I learned about them (right here in SO) — Barranka, Jul 03 '14 at 21:45

score 9 · Accepted Answer · edited May 23 '17 at 10:29

You can, using temporary variables:

select
    @status1 := (case 
        when user.id < 500 then 'awesome' 
        else 'lame' 
    end) as `status`,
    (case 
        when @status1 = 'awesome' then 'You rock' 
        else 'You stink' 
    end) as message
from
    user;

Some things you must know about temp variables:

They are always preceded by @
- Avoid using reserved words, just in case (that's the reason I named the variable @status1
After the @ symbol, they must begin with a letter, and must not have spaces
When you update them in a single query, they are updated "left-to-right" (talking about columns) and "first-to-last" (talking about rows). That can help you calculate cummulative sums or averages.

Example (for point 2):

select @t := 1, @t := @t + 1;

@t1 | @t2
----+----
1   | 2

Example (for point 3):

select myTable.x, @t := @t + myTable.x as cummulative_x
from 
    (select @t := 0) as init, -- You need to initialize the variable, 
                              -- otherwise the results of the evaluation will be NULL
    myTable
order by myTable.x -- Always specify how to order the rows,
                   -- or the cummulative values will be quite odd
                   -- (and maybe not what you want)
;

x  | cummulative_x
---+---------------
1  | 1
1  | 2
2  | 4
3  | 7

Temporary variables can help you do some awesome things... feel free to play around ;)

Update

If you want to define conditions on the result of this query, there are two ways to do it:

Use the above query as a data-source for a second query (i.e. make it a subquery in the from clause of another query
Create a temp table and query on it

Option 1:

select a.*
from (
    -- The query with temp variables defined
)
where -- ATTENTION: you need to write the references to the column names of the subquery

Option 2: (my personal favorite)

drop table if exists temp_my_temp_table;
create temporary table temp_my_temp_table
    select
        @status1 := (case 
            when user.id < 500 then 'awesome' 
            else 'lame' 
        end) as `status`,
        (case 
            when @status1 = 'awesome' then 'You rock' 
            else 'You stink' 
        end) as message
    from
        user;
-- Add all appropriate indexes to this newly created table:
-- alter table temp_my_temp_table
--     add index idx_status(`status`),
--     add index idx_mess(message);
-- Make your queries on this new temp table
select * from temp_my_temp_table
-- where ...
;

Things you must know about a temp table:

They are created on RAM (by default, and only if the table is not too big)
They are only visible to the connection that created it
They are eliminated once the connection that created it is closed (or terminated in any way)
You can't use it more than once in a FROM clause. Other than that, you can use it as any other table in your database

Another update

Just by chance I came across this question and its answer. If you want to use the result of your column (calculated with temp variables) as a condition, MySQL allows this:

select
    @status1 := (case 
        when user.id < 500 then 'awesome' 
        else 'lame' 
    end) as `status`,
    (case 
        when @status1 = 'awesome' then 'You rock' 
        else 'You stink' 
    end) as message
from
    user
having
    `status` = 'awesome';

Instead of using where use having, and refer not to the temp variable, but to the alias of the column.

Thanks for the answer. It looks like I cannot use temporary variables in the `WHERE` clause, is this correct? For example, if I add `WHERE @status1 = 'awesome'` to the end of my query, I get zero results. — Leo Galleguillos, Jul 07 '14 at 17:20
@Leo Indeed, temp variables can't be used in the where clause. However, there's a work-around if you need that... Updating to include the "trick" — Barranka, Jul 07 '14 at 17:27
Okay, thank you so much for the update. It looks like if I want to use multiple `CASE` statements and filter on temporary variables, I'll have to use temporary tables or subqueries after all :| — Leo Galleguillos, Jul 07 '14 at 17:41
@Leo Yes... but you can use the temp variables to make all the complex substitutions (or calculations), and then use the result to perform simpler queries. There's no free lunch, but you can split a complex process in a few simple steps with some calculations, and your final query will be a very simple one. "Divide and conquer" works on 90% of times (at least), and is frequently faster than trying to do things in a single over-complex query — Barranka, Jul 07 '14 at 17:44
@Leo Check new update... there's a third work-around yor what you want — Barranka, Jul 07 '14 at 17:50

MySQL CASE based on previous CASE value

1 Answers1