title: Where Case Statement trouble
body: <p>I have a sql server 2008 table, let's say "alpha", with three columns. They are [ID], [col1] and [col2].</p>
  
  <pre><code>id|col1|col2
  1 |X   |john1X|
  1 |Y   |john1Y|
  1 |Z   |john1Z|
  2 |X   |john2|
  3 |Y   |john3|
  4 |X   |john4|
  </code></pre>
  
  <p>Each ID might have multiple entries. If col1 contains 'X', I would like that row displayed. If there is no ID with 'X' in col1, I would like 'Y' chosen. Otherwise the row should not come up at all.</p>
  
  <p>For the above sample data, the expected output would be below. </p>
  
  <pre><code>id|col1|col2
  1 |X   |john1X|
  2 |X   |john2|
  3 |Y   |john3|
  4 |X   |john4|
  </code></pre>
  
  <p>I've been trying to get this code to work,</p>
  
  <pre><code>select * from alpha
  where col1 = case
               when exists(select * from alpha where col1 = 'X') then 'X'
               else 'Y'
               end
  </code></pre>
  
  <p>however I keep getting the below output no matter how I rewrite the code.</p>
  
  <pre><code>id|col1|col2
  1 |X   |john1X
  2 |X   |john2 
  4 |X   |john4
  </code></pre>