title: MySQL query using CASE to SELECT multiple columns
body: <p>I have the following query which works correctly:</p>
  
  <pre><code>SELECT Future.enemy_type,
  CASE WHEN Future.enemy_type = 'square' THEN Users.color
  ELSE ''
  END AS color,
  CASE WHEN Future.enemy_type = 'square' THEN Users.user_ID
  ELSE ''
  END AS ID,
  CASE WHEN Future.enemy_type = 'headquarters' THEN Users.username
  ELSE ''
  END AS username,
  CASE WHEN Future.enemy_type = 'headquarters' THEN Users.home_lat
  ELSE ''
  END AS lat
  FROM Future
  LEFT JOIN Users ON Future.user_ID_affected = Users.user_ID
  WHERE Future.time &gt; 1539503510
  AND Future.time &lt;= 1539503512
  AND Future.user_ID = 10;
  </code></pre>
  
  <p>However, is there a more efficient way to write it? I need to SELECT a few values based on the conditional (e.g. square or headquarters), but I understand that CASE can only return 1 value. However, it seems inefficient to check the condition with a new CASE statement each time I want to return one value.</p>
  
  <p>Some people on SO have suggested using JOIN statements for similar queries, but I'm not sure how this would work with multiple conditionals?</p>
  
  <p>Thanks in advance.</p>