title: Geting a CASE statement to work with subquery/join
body: <p>I am using MySQL 5.6.</p>
  
  <p>I have a query for a table <code>view_listInvoices</code> that has a column <code>state</code> which is an integer from 1-7, but as of now is mostly <code>NULL</code>.</p>
  
  <p>What I'd like is to query for this state, and if it is some values, to display <code>---</code>, and if it is other values, to JOIN on a description table, <code>descinvoicestate</code> and display the description.</p>
  
  <pre><code>State        Desired Value
  NULL         ----
  1            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
  2            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
  3            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
  4            ---
  5            ---
  6            ---
  7            descinvoice.description where view_listInvoice.state = descinvoicestate.idx
  </code></pre>
  
  <p>The <code>descinvoicestate</code> table is one of id's and a description column and thats it.</p>
  
  <p>My current query which is not working is </p>
  
  <pre><code>SELECT
  otherColumns...,
  (SELECT CASE WHEN `view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6) THEN "---"
  ELSE (SELECT `descinvoicestate`.`description` 
  FROM `view_listInvoices`  
  JOIN `descinvoicestate`  
  ON `descinvoicestate`.`idx` = `view_listInvoices`.`state`) END) as 'Prep'
  
  FROM `view_listInvoices`
  </code></pre>
  
  <p>I keep getting that I have  syntax error but I don't know what is wrong.  Help?</p>