title: SQL Unique Records with multiple joins
body: <p>I have three tables I need to join. The first two are easy and based on the same key. A simple outer join will work. The third is tricky. It has a similar key but must be matched on the left 14 of one key. This produces duplicates which I do not want. Essentially, I want the TOP 1 FROM Table3 that matches the IDs correctly.</p>
  
  <p>Using T-SQL.</p>
  
  <p>The tables are large and complicated, so I've outlined below the a simple example of the tables with sample data and the query I'm running now.</p>
  
  <p>Table1</p>
  
  <pre><code>ID1          ID2                 Field1    Field2    Field3    Field4
  0000000000   00000000000000a     Info1     Info2     Info3     Info4
  2222222222   11111111111111b     Info1     Info2     Info3     Info4
  1111111111   22222222222222c     Info1     Info2     Info3     Info4
  </code></pre>
  
  <p>Table2</p>
  
  <pre><code>ID1          ID2                 Field1    Field2  
  0000000000   00000000000000a     Info5     Info6  
  2222222222   11111111111111b     Info5     Info6  
  1111111111   22222222222222c     Info5     Info6 
  </code></pre>
  
  <p>Table</p>
  
  <pre><code>ID1          ID2                 Field1    
  0000000000   00000000000000a     Info7 
  0000000000   00000000000000b     Info7  
  0000000000   00000000000000c     Info7  
  2222222222   11111111111111b     Info7  
  2222222222   11111111111111d     Info7  
  1111111111   22222222222222c     Info7  
  </code></pre>
  
  <p>Query</p>
  
  <pre><code>SELECT
  Table1.ID1,
  Table1.ID2,
  Table1.Field1,
  Table1.Field2,
  Table1.Field3,
  Table1.Field4,
  Table2.Field1,
  Table2.Field2,
  Table3.Field1
  FROM Table1
  LEFT JOIN Table2
  ON Table1.ID1=Table2.ID2 AND Table1.ID2=Table2.ID2
  LEFT JOIN Table3
  ON Table1.ID1=Table3.ID2 AND LEFT(Table1.ID2,14)=LEFT(Table3.ID2,14)
  </code></pre>
  
  <p>The response I get is</p>
  
  <pre><code>0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
  0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
  0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
  2222222222 11111111111111b Info1 Info2 Info3 Info4 Info5 Info6 Info7
  2222222222 11111111111111b Info1 Info2 Info3 Info4 Info5 Info6 Info7
  1111111111 22222222222222c Info1 Info2 Info3 Info4 Info5 Info6 Info7
  </code></pre>
  
  <p>What I want is</p>
  
  <pre><code>0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
  2222222222 11111111111111b Info1 Info2 Info3 Info4 Info5 Info6 Info7
  1111111111 22222222222222c Info1 Info2 Info3 Info4 Info5 Info6 Info7
  </code></pre>