Link to home
Start Free TrialLog in
Avatar of mojeaux
mojeauxFlag for United States of America

asked on

Remove duplicates from Left Outer Join

Hi -
How do I remove duplicate Usernames from the query below.   I changed the query from a cross join to a left outer join to eliminate massive amounts of records.   Now I have the issue of retreiving multiple usernames (I'm grouping by MEMBEROF in the crystal report)

WITH EmployeeList AS
(SELECT DISTINCT   A.DistinguishedName,
                      A.AccountName,
                      A.DisplayName,
                      A.Type,
                      B.MEMBEROF,
                      B.USERNAME
FROM         ADUsersAndGroups A                
LEFT OUTER JOIN GROUPS B ON A.DistinguishedName = B.MEMBEROF
                 
UNION

SELECT DISTINCT  A.DistinguishedName,
                      A.AccountName,
                      A.DisplayName,
                      A.Type,
                      B.MEMBEROF,
                     B.USERNAME
FROM         ADUsersAndGroups A
LEFT OUTER JOIN GROUPS B ON A.AccountName = B.USERNAME)
SELECT * FROM EmployeeList
WHERE DisplayName is not NULL;


I'm trying to acheive the following output in the report:
Where user's are grouped by groupname.
MEMBEROF  (GROUPNAME)
          USERNAME (USERID)        DISPLAYNAME (User name)


However, I'm currently getting output were Display name is blank or populated with group information and the Username is diplaying multiple times.

Any help would be greatly appreciated.    Thanks!!  Mojeaux
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
please post some example of the data/records you get duplicated...
I think instead of 'UNION', you can use OR condition in left outer join:
SELECT DISTINCT   A.DistinguishedName, 
                      A.AccountName, 
                      A.DisplayName, 
                      A.Type,
                      B.MEMBEROF, 
                      B.USERNAME
FROM ADUsersAndGroups A                 
LEFT OUTER JOIN GROUPS B ON (A.DistinguishedName = B.MEMBEROF OR A.AccountName = B.USERNAME)

Open in new window

Avatar of Mike McCracken
Mike McCracken

The union is required since that makes it recursive.

mlmcc
Avatar of mojeaux

ASKER

Thank you!  I was able to reach the same conclusion....thank goodness for you guys on EE!  Y'all are my 'goto' guys!