title: Removing duplicates from SQL Join
body: <p>The following is a hypothetical situation this which is close to my real problem. <code>Table1</code></p>
  <pre><code>recid   firstname    lastname   company
  1       A             B          AAA
  2       D             E          DEF
  3       G             H          IJK
  4       A             B          ABC
  </code></pre>
  <p>I have a table2 that looks like this</p>
  <pre><code>recid   firstname    lastname   company
  10      A             B          ABC
  20      D             E          DEF
  30      M             D          DIM
  40      A             B          CCC
  </code></pre>
  <p>Now if I join the table on <code>recid</code>, it will give <code>0</code> results, there will be no duplicates because <code>recid</code> is unique. But if I join on <code>firstname</code> and <code>lastname</code> columns, which are not unique and there are duplicates, I get duplicates on inner join. The more columns I add on join, the worse it becomes (more duplicates are created).</p>
  <p>In the above simple situation, how can I remove duplicates in the following query? I want to compare <code>firstname</code> and <code>lastname</code>, if they match, I return <code>firstname</code>, <code>lastname</code> and <code>recid</code> from <code>table2</code></p>
  <pre><code>SELECT DISTINCT * FROM
  (SELECT recid, first, last FROM table1) a
  INNER JOIN
  (SELECT recid, first,last FROM table2) b
  ON a.first = b.first
  </code></pre>
  <hr />
  <p>The script is here if anyone wants to play with it in future</p>
  <pre><code>CREATE TABLE table1 (recid INT NOT NULL PRIMARY KEY, first varchar(20), last varchar(20), company varchar(20))
  CREATE TABLE table2 (recid INT NOT NULL PRIMARY KEY, first varchar(20), last varchar(20), company varchar(20))
  
  INSERT INTO TABLE1 VALUES(1,'A','B','ABC')
  INSERT INTO TABLE1 VALUES(2,'D','E','DEF')
  INSERT INTO TABLE1 VALUES(3,'M','N','MNO')
  INSERT INTO TABLE1 VALUES(4,'A','B','ABC')
  
  INSERT INTO TABLE2 VALUES(10,'A','B','ABC')
  INSERT INTO TABLE2 VALUES(20,'D','E','DEF')
  INSERT INTO TABLE2 VALUES(30,'Q','R','QRS')
  INSERT INTO TABLE2 VALUES(40,'A','B','ABC')
  </code></pre>