title: SQL How to use CASE with a NOT EXISTS statement
body: <p>I have quite a complex query in SQL where I am pulling a load of information from various tables and doing various joins. I want to pull a column which checks whether the particular tradeId is contained in 2 different tables. I'm getting stuck with how to do this properly though. </p>
  
  <p>The below code gives me all the TradeIds from the Trade table that are NOT in the TCM (which is just a combination of 2 tables). However I would like ALL the trades from the Trade table and then a column to indicate whether it is found in TCM or not. </p>
  
  <p>I know this would be done with a CASE WHEN query but I'm confused how to structure it so that it fits in the CASE WHEN. </p>
  
  <pre><code>With subCA As
  (Select distinct OTPTradeId, ConfoAuditSenderRef from ConfirmationAudit where ConfoAuditSenderRef like 'HBEUM%'),
  TCM As
  (Select distinct OTPTradeID from subCA union ALL select TradeId from subCA inner join ConfirmationSent on (OTPTradeId = ConfoId 
  AND ConfoAuditSenderRef like 'HBEUMN%'))
  select TradeId from Trade where NOT EXISTS (Select OtpTradeId from TCM where OtpTradeId = TradeId) 
  and TradeDate = '17 jun 2013'
  </code></pre>
  
  <p>Heres my attempt to fit it in a CASE WHEN statement but I get an error because the NOT EXISTS is not allowed without a WHERE I believe. But what I am after is something like this. If I use NOT IN it becomes painstakingly slow like 5 minutes plus and this is part of an larger query and I dont want it to take so long - if possible!</p>
  
  <pre><code>With subCA As
  (Select distinct OTPTradeId, ConfoAuditSenderRef from ConfirmationAudit where ConfoAuditSenderRef like 'HBEUM%'),
  TCM As
  (Select distinct OTPTradeID from subCA union ALL select TradeId from subCA inner join ConfirmationSent on (OTPTradeId = ConfoId 
  AND ConfoAuditSenderRef like 'HBEUMN%'))
  select TradeId,
  CASE WHEN
      (TradeId NOT EXISTS (Select OtpTradeId from TCM where OtpTradeId = TradeId) Then 'Y' Else 'N' End As 'TCM' 
  from Trade
  WHERE TradeDate = '17 jun 2013'
  </code></pre>