troubleshooting Question

SQL Query for finding duplicates by multiple fields

Avatar of JRSchaefer
JRSchaefer asked on
ColdFusion LanguageMicrosoft SQL Server 2008SQL
3 Comments2 Solutions786 ViewsLast Modified:
How do I create a query to find duplicate records based on two fields?  For example I need to find all records that are duplicates based on the first and last name being a duplicate but I need to display all information regarding those duplicates not just the first and last name.

Bob Smith-123 Main Street, City, MN 66666
Bob Smith-777 Center Street, City, MN 55555

I tried:

<cfquery name="name" datasource="datasource">
        SELECT  *
        FROM    CMS_Profile
        WHERE  PLName <> ''
        AND       PFName <> ''
        AND   PFName IN (
               SELECT PFName
                FROM      CMS_Profile
                GROUP BY PFName
                HAVING count(PFName) > 1
                )
        AND      PLName IN (
                SELECT PLName
                FROM      CMS_Profile
                GROUP BY PLName
                HAVING count(PLName) > 1
                )
        ORDER BY PLName, PFName
    </cfquery>

Any help is appreciated!
ASKER CERTIFIED SOLUTION
gdemaria

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros