title: Can I use CASE statement in a JOIN condition?
body: <p>The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between <code>sys.partitions</code> and <code>sys.allocation_units</code> depends on the value of <code>sys.allocation_units.type</code>. So to join them together I would write something similar to this:</p>
  
  <pre><code>SELECT  *
  FROM    sys.indexes i
          JOIN sys.partitions p
              ON i.index_id = p.index_id 
          JOIN sys.allocation_units a
              ON CASE
                 WHEN a.type IN (1, 3)
                     THEN a.container_id = p.hobt_id 
                 WHEN a.type IN (2)
                     THEN a.container_id = p.partition_id
                 END 
  </code></pre>
  
  <p>But the upper code gives a syntax error. I guess that's because of the <code>CASE</code> statement. 
  Can anyone help to explain a little?</p>
  
  <hr>
  
  <p>Add error message:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.</p>
  </blockquote>
  
  <p><img src="https://i.stack.imgur.com/HYmiS.jpg" alt="this is the image"></p>