Will "the Mighty" Strohl

Finding Duplicate Display Names in DotNetNuke

There may be a time where you’ll have to find display names that are not unique.  I recently had to do this for an Active Social implementation.  (I have done a few of those now.)  There could be any number of reasons for you to do this.  You might need to for the same reason as me, to accomplish a business objective, or you may simply be curious. 

Regardless to why you feel you need to find the user accounts with matching display names, doing so is pretty simple.  There is a simple query that you can run to get the results you are looking for.  The good news is, that with a few simple changes, you can use this same query to do similar checks in other data tables.

Here is the query I used to find the non-unique display names in my DotNetNuke® site:

SELECT u.*
FROM [dbo].[Users] u
LEFT JOIN
(
    SELECT [DisplayName], MIN([UserId]) AS MinID
    FROM [dbo].[Users] 
    GROUP BY [DisplayName]
) dt ON u.[DisplayName] = dt.[DisplayName] AND u.[UserId] = dt.MinID
WHERE dt.[DisplayName] IS NULL;

The previous code snippet is a T-SQL query that returned a listing of DNN user accounts that had the same value.  If I wanted to do the same thing to find out which accounts were using the same e-mail address, the query might look like this:

SELECT u.*
FROM [dbo].[Users] u
LEFT JOIN
(
    SELECT [Email], MIN([UserId]) AS MinID
    FROM [dbo].[Users] 
    GROUP BY [Email]
) dt ON u.[Email] = dt.[Email] AND u.[UserId] = dt.MinID
WHERE dt.[Email] IS NULL;

See?  It’s that easy!  However, you SQL Server 2000 folks will want to remove the semicolons in my snippets. 

I can take this one step further for those of you who might not have direct access to your SQL Server, and instead use the Host SQL Module.  We can change either snippet, by using a couple of standard tokens:

SELECT u.*
FROM {databaseOwner}[{objectQualifier}Users] u
LEFT JOIN
(
    SELECT [DisplayName], MIN([UserId]) AS MinID
    FROM {databaseOwner}[{objectQualifier}Users] 
    GROUP BY [DisplayName]
) dt ON u.[DisplayName] = dt.[DisplayName] AND u.[UserId] = dt.MinID
WHERE dt.[DisplayName] IS NULL;

There is one warning that you should take note of though… My queries above were designed for a standalone DNN installation.  If your site has more than one portal in it, you will see all users from all portals returned.  In order to prevent that, you would need to perform an additional join on our examples.

I hope this helps!



blog comments powered by Disqus