title: SQL Using inner query in left join
body: <p>I have the below two tables ONE &amp; TWO </p>
  
  <pre><code>ONE           TWO
  
  ID            ID  CODE  
  -----         ---------
  1             1    1
  2             1    2
  3             2    1
  4             3    1  
  5             3    2  
                3    3
  </code></pre>
  
  <h2>Output required</h2>
  
  <pre><code>ID    CODE  
  ----------  
  1     2  
  2     1  
  3     3  
  4     NULL  
  5     NULL  
  </code></pre>
  
  <p>I used the below query but it is throwing the error "<strong>An ON clause associated with a JOIN operator or in a MERGE statement is not valid.</strong>"</p>
  
  <pre><code>SELECT  A.ID
         ,B.CODE
  FROM   ONE A 
  LEFT JOIN
         TWO B
  ON     A.ID = B.ID
     AND B.CODE = (
                     SELECT 
                           MAX(Z.CODE)
                     FROM TWO Z
                     WHERE Z.ID = A.ID
              )
  </code></pre>