title: select CASE statement based on two tables
body: <p>MS SQL Server 2008R2 Management Studio</p>
  
  <p>I am running a <code>SELECT</code> on two tables. I'll simplify it to the part where I'm having trouble. I need to modify the <code>SELECT</code> results to a certain format for a data import.  My <code>CASE</code> statement works fine until I get to the point that I need to base the <code>WHEN ... THEN...</code> on a different table column</p>
  
  <pre><code>TABLE1
  -----------------
  name   |  tag   | code
  -----------------------
  name1  |   N    | 100
  name2  |   N    | 100
  name3  |   N    | 200
  name4  |   Y    | 100
  name5  |   N    | 400
  name6  |   N    | 700
  
  CODES
  -------------------------
  code |   desc
  -------------------------
  100 | string1
  200 | string2
  300 | string2
  400 | string2
  700 | string2
  
  SELECT name,
  Case CODES.desc
  when 'string1' then 'String 1'
  when 'string2' then 'String 2'
  when 'string3' then 'String 3'
  when 'string4' then 'String 4'
  END as description
  FROM TABLE1
  join CODES on TABLE1.code = CODES.code
  </code></pre>
  
  <p>This works fine. The problem is if <code>TABLE1.tag = Y</code>, then description needs to be <code>'Other string'</code> which is not in the <code>CODES</code> table</p>
  
  <p>I tried adding:</p>
  
  <pre><code>Case CODES.desc
  .....
  when TABLE1.tag = Y then CODES.desc 'Other String'
  </code></pre>
  
  <p>but it didn't work.</p>