Remove duplicate left outer join

Question

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

Result

Query Output

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.

[Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) — marc_s, Dec 08 '20 at 18:27
Possible duplicate question? - https://stackoverflow.com/questions/30410622/prevent-duplicate-values-in-left-join — ryanwebjackson, Dec 09 '20 at 01:33

score 2 · Accepted Answer · answered Dec 08 '20 at 18:33

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]

Remove duplicate left outer join

1 Answers1