title: Joining Non-Distinct and Distinct Tables
body: <p>Using MS SQL SERVER 2008</p>
  
  <p>I have the following scenario below:</p>
  
  <pre><code>===================
  |     Table A     |
  ===================
  |id      |desc      |code 
  | 1      |ballpen   |1010
  | 2      |pencil    |1010
  | 3      |stabilo   |1010
  | 4      |pins      |1011
  | 5      |clips     |1011
  
  ===================
  |      Table B    |
  ===================
  | id     |code
  | 1010   |AAA
  | 1011   |BBB
  | 1013   |CCC
  </code></pre>
  
  <p>What I need to accomplish is something below:</p>
  
  <pre><code>===================
  |      Table C    |
  ===================
  |id   |desc    |code  |code 2
  | 1   |ballpen |1010  |AAA
  | 2   |pencil  |1010  |AAA
  | 3   |stabilo |1010  |AAA
  | 4   |pins    |1011  |BBB
  | 5   |clips   |1011  |BBB
  </code></pre>
  
  <p>Here's what I have so far, unfortunately the <code>LEFT JOIN</code> is causing the final query output to bloat</p>
  
  <pre><code>select *
  from table a
    left join table b on a.code = b.id
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>========
  Table D
  ========
  |id |desc    |code |code2
  |1  |ballpen |1010 |AAA
  |1  |ballpen |1010 |AAA
  |1  |ballpen |1010 |AAA
  |2  |pencil  |1010 |AAA
  |2  |pencil  |1010 |AAA
  |2  |pencil  |1010 |AAA
  |3  |stabilo |1010 |AAA
  |3  |stabilo |1010 |AAA
  |3  |stabilo |1010 |AAA
  |4  |pins    |1011 |BBB
  |4  |pins    |1011 |BBB
  |5  |clips   |1011 |BBB
  |5  |clips   |1011 |BBB
  </code></pre>