Edit Question - KnowledgeHub

Title Body

I'm creating a joined view of two tables, but am getting unwanted duplicates from table2. 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). 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 Is there a way to do something similar with joins to speed up performance? I'm using SQL Server 2008 R2. 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). 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 table examples 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 In the above example returned records should be id5/P4/AB4, id2/P10/AB2, and (id1/P1/AB1 OR id1/P5/AB5) My search came up with similar questions, but none that resolved my problem. link , link Thanks in advance for your help. This is my first post so let me know if I've broken any rules.

Tags (comma-separated) Cancel