title: MySQL CASE based on previous CASE value
body: <p>In MySQL, is it possible to have two <code>CASE</code> statements in the <code>SELECT</code> clause, where the second <code>CASE</code> statement relies on the first <code>CASE</code> statement?</p>
  
  <p>For example, consider the following query:</p>
  
  <pre><code>SELECT CASE WHEN `user`.`id` &lt; 500 THEN 'awesome' ELSE 'lame' END
      AS `status`
  
       ,  CASE WHEN `status` = 'awesome' THEN 'You rock' ELSE 'You stink' END
      AS `message`
  
    FROM `user`
  </code></pre>
  
  <p>Basically, the user ID determines the status, and then the status determines the message.</p>
  
  <p>However, as you might have guessed, this query generates this error:</p>
  
  <pre><code>Unknown column 'status'
  </code></pre>
  
  <p>The only solution I have found so far is two generate a temporary table, view, or subquery, and then the <code>message</code> is determined by the <code>status</code> returned in this subquery.</p>
  
  <p>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!</p>