SQL - remove duplicates from left join
Your original query is producing arbitrary values for the two columns (the use of
top with no
order by). You can get the same effect with this:
SELECT t1.OBJECTID, t1.PKID, t2.ProjectName, t2.AsbuiltFROM dbo.table1 t1 LEFT JOIN (SELECT FKID, min(ProjectName) as ProjectName, MIN(asBuilt) as AsBuilt FROM dbo.table2 group by fkid ) t2 ON t1.PKID = t2.FKID
This version replaces the
distinct with a
To get a truly random row in SQL Server (which your syntax suggests you are using), try this:
SELECT t1.OBJECTID, t1.PKID, t2.ProjectName, t2.AsbuiltFROM dbo.table1 t1 LEFT JOIN (SELECT FKID, ProjectName, AsBuilt, ROW_NUMBER() over (PARTITION by fkid order by newid()) as seqnum FROM dbo.table2 ) t2 ON t1.PKID = t2.FKID and t2.seqnum = 1
This assumes version 2005 or greater.
If you want described result, you need to use
INNER JOIN and following query will satisfy your need:
SELECT t1.OID, t1.PKID, MAX(t2.ProjectName) AS ProjectName, MAX(t2.Asbuilt) AS AsbuiltFROM table1 t1JOIN table2 t2 ON t1.PKID = t2.FKIDGROUP BY t1.OID, t1.PKID
If you want to see all rows from left table (table1) whether it has pair in right table or not, then use
LEFT JOIN and same query will gave you desired result.
This construction has good performance, and you dont need to use subqueries.