title: MSSQL Operation within OUTER JOIN
body: <p>I am trying to join two tables using a unique value, however in one table it is part of a field with different lengths, e.g.:</p>
  
  <p>Table one:</p>
  
  <pre><code>       Block_Run_No  Vehicle_No
  200001.412.20110104        1001
   20002.412.20110104        1002
  </code></pre>
  
  <p>Table 2</p>
  
  <pre><code>Block_No  Departure
    200001       3600
     20002       7200
  </code></pre>
  
  <p>Now I want to join them by Block_No and try this:</p>
  
  <pre><code>SELECT Block_No, Vehicle_No, Departure
  FROM Table1,
      FULL OUTER JOIN Table2 A on LEFT(Table1.Block_Run_No,CHARINDEX('.',Table1.Block_Run_No)-1) = A.Block_No
  </code></pre>
  
  <p>However, It claims to not recognize the expression <code>Table1.Block_Run_No</code> within the JOIN. Anyone can help me out here? </p>
  
  <p>Thanks!</p>