title: SQL - remove duplicates from left join
body: <p>I'm creating a joined view of two tables, but am getting unwanted duplicates from table2.<br>
  For example: table1 has 9000 records and I need the resulting view to contain exactly the same; table2 may have multiple records with the same FKID but I only want to return one record (random chosen is ok with my customer).  I have the following code that works correctly, but performance is slower than desired (over 14 seconds).</p>
  
  <pre><code>SELECT     
      OBJECTID
      , PKID
      ,(SELECT TOP (1) SUBDIVISIO
          FROM dbo.table2 AS t2
          WHERE (t1.PKID = t2.FKID)) AS ProjectName
      ,(SELECT TOP (1) ASBUILT1
          FROM dbo.table2 AS t2
          WHERE (t1.PKID = t2.FKID)) AS Asbuilt
  FROM dbo.table1 AS t1
  </code></pre>
  
  <p>Is there a way to do something similar with joins to speed up performance?<br>
  I'm using SQL Server 2008 R2.<br>
  I got close with the following code (~.5 seconds), but 'Distinct' only filters out records when all columns are duplicate (rather than just the FKID).</p>
  
  <pre><code>SELECT
      t1.OBJECTID
      ,t1.PKID
      ,t2.ProjectName
      ,t2.Asbuilt
  FROM dbo.table1 AS t1
      LEFT JOIN (SELECT
          DISTINCT FKID
          ,ProjectName
          ,Asbuilt
          FROM dbo.table2) t2
      ON t1.PKID = t2.FKID
  </code></pre>
  
  <p>table examples  </p>
  
  <pre><code>table1          table2
  
  OID, PKID       FKID, ProjectName, Asbuilt
  1, id1          id1, P1, AB1
  2, id2          id1, P5, AB5
  3, id4          id2, P10, AB2
  5, id5          id5, P4, AB4
  </code></pre>
  
  <p>In the above example returned records should be id5/P4/AB4, id2/P10/AB2, and (id1/P1/AB1 OR id1/P5/AB5)</p>
  
  <p>My search came up with similar questions, but none that resolved my problem.  <a href="https://stackoverflow.com/questions/757957/restricting-a-left-join">link</a>, <a href="https://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns">link</a><br>
  Thanks in advance for your help.  This is my first post so let me know if I've broken any rules.</p>