title: SQL Server subqueries in CASE THEN with multiple JOINS
body: <p>I've searched high and low for an answer but I'm not finding one...</p>
  
  <p>I'm trying to run a subquery inside a <code>CASE THEN</code> statement based on qualifier, that goes something like this:</p>
  
  <pre><code>SELECT
      CASE
          WHEN cbpotm.DOLCheck LIKE '%&lt;%'
          THEN
          (
              SELECT
                  ...
              FROM #AnotherTempTable AS CB
              LEFT OUTER JOIN ...
              LEFT OUTER JOIN ...
              LEFT OUTER JOIN ...
              WHERE CONDITION AND CONDITION
              AND CONDITION IN ( 1, 2, 3, 4 )
              AND CAST(SUBSTRING(CAST(tflr.DateKey AS VARCHAR), 1, 4) + '-' + SUBSTRING(CAST(tflr.DateKey AS VARCHAR), 5, 2) + '-' + SUBSTRING(CAST(tflr.DateKey AS VARCHAR), 7, 2) AS DATE) BETWEEN CB.SomeDate AND CB.SomeOtherDate
              GROUP BY FIELD, FIELD
          )
          END
  FROM    
  #TempTable cbpotm
  </code></pre>
  
  <p>Apparently I'm missing the mark because I'm not allowed to have more than one <code>JOIN</code> inside the <code>CASE THEN</code> without introducing the statement with an <code>EXISTS</code> clause.<br>
  I'm not trying to compare the statement to anything so I can't see why I should only use a single field to match against?
  I also don't see why I should use <code>EXISTS</code> because I'm not trying to perform validation...</p>