mojeaux
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
The union is required since that makes it recursive.
mlmcc
mlmcc
ASKER
Thank you! I was able to reach the same conclusion....thank goodness for you guys on EE! Y'all are my 'goto' guys!