Remove duplicate left outer join

Question

I need help to merge 2 duplicate rows from the below result from my sql query.

Result

Query

SELECT DISTINCT
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName],
    c.UserName,
    d.RoleName  
FROM 
    [dbo].[Location] a
LEFT OUTER JOIN 
    [dbo].[UserLocation] b ON a.LocationID = b.LocationID
LEFT OUTER JOIN 
    [dbo].[User] c ON b.UserID = c.UserID AND c.RoleID = 2
LEFT OUTER JOIN 
    [dbo].[Role] d ON c.RoleID = d.RoleID

If you can see there are 2 rows with LocationId 3057, one with Role Admin and One with NULL. I want to merge them and want to show only one row with Role Admin.

Thank you for looking in to this.

Answer 1

Try this:

SELECT
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName],
    MAX(c.UserName) UserName,
    MAX(d.RoleName ) RoleName 
FROM 
    [dbo].[Location] a
LEFT OUTER JOIN 
    [dbo].[UserLocation] b ON a.LocationID = b.LocationID
LEFT OUTER JOIN 
    [dbo].[User] c ON b.UserID = c.UserID AND c.RoleID = 2
LEFT OUTER JOIN 
    [dbo].[Role] d ON c.RoleID = d.RoleID
GROUP BY 
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName]