title: How do you use WHERE NOT EXISTS with WITH?
body: <p>I've got two sets with the same columns and I'm trying to get a list of items in the first set that do not exist in the second. The second list is built from a recursive select that uses <code>WITH</code> and I can't figure out how to use that with <code>WHERE NOT EXISTS</code>.</p>
  
  <pre><code>SELECT *
  FROM (
      SELECT DISTINCT [Fruit]
          ,[Color]
      FROM [dbo].[CostCentreMapping]
      WHERE [Color] IS NOT NULL
          AND [Color] &lt;&gt; ''
          AND LEFT([Fruit], 1) = 'C'
      ) AS a
  WHERE NOT EXISTS (
          --Incorrect syntax error occurs here
          WITH tmp(Fruit, DataItem, ManyColors) AS (
                  SELECT Fruit
                      ,CAST(LEFT(ManyColors, CHARINDEX(',', ManyColors + ',') - 1) AS VARCHAR(200))
                      ,CAST(STUFF(ManyColors, 1, CHARINDEX(',', ManyColors + ','), '') AS VARCHAR(200))
                  FROM ExecutiveSummaryDisplay
  
                  UNION ALL
  
                  SELECT Fruit
                      ,CAST(LEFT(ManyColors, CHARINDEX(',', ManyColors + ',') - 1) AS VARCHAR(200))
                      ,CAST(STUFF(ManyColors, 1, CHARINDEX(',', ManyColors + ','), '') AS VARCHAR(200))
                  FROM tmp
                  WHERE ManyColors &gt; ''
                  )
          SELECT DISTINCT Fruit AS [Fruit]
              ,DataItem AS [Color]
          FROM tmp
          WHERE Fruit IS NOT NULL
              AND Fruit &lt;&gt; ''
              AND DataItem IS NOT NULL
              AND DataItem &lt;&gt; ''
          )
  </code></pre>