title: How to get SQL Left Join to return only one row?
body: <p>Using SQL Server 2008, I am trying to do a left join between table 1 and table 2, on a column c1. </p>
  
  <p>The idea is that, from the left table (TABLE 1), I grab each and every row. From the right one (TABLE 2) I only get ONE value. </p>
  
  <pre><code>Table 1    
  --------------------
  document          varchar(30)
  
  Table 2    
  --------------------
  idTable2          int(30)
  document          varchar(30)
  
  Sample data TABLE 1
  --------------------
  3846922
  2000762
  3064627
  
  Sample data TABLE 2
  --------------------
  1            3846922
  2            2000762
  3            3064627
  4            2000762
  5            3846922 
  
  Sample Result
  --------------------
  3846922     1
  2000762     2
  3064627     3
  </code></pre>
  
  <p>I used <a href="https://stackoverflow.com/questions/2510349/using-left-join-to-only-selection-one-joined-row">this</a> post as a guide, but couldn't get quite to the desired result. Up till now, I get many values of the left table and many of the right one. Any ideas? The following is my sql query:</p>
  
  <pre><code>SELECT t1.document, t2.idTable2
  FROM Table1 t1
  LEFT JOIN Table2 t2
  ON t1.document = 
  (
     SELECT TOP 1 t2_aux.document
     FROM Table2 t2_aux
     WHERE t2_aux.document = t1.document
     ORDER BY t2_aux.document DESC
  )
  </code></pre>