Solved

Remove duplicates from Left Outer Join

Posted on 2013-06-12
5
1,827 Views
Last Modified: 2013-06-12
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
0
Comment
Question by:mojeaux
5 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39242734
I think you want to limit the first query to type = GROUP and the second to type = user

Can a group have a group?

mlmcc
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39242738
please post some example of the data/records you get duplicated...
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39242758
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

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39243324
The union is required since that makes it recursive.

mlmcc
0
 

Author Closing Comment

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question