Avatar of JRSchaefer
JRSchaefer
 asked on

SQL Query for finding duplicates by multiple fields

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!
ColdFusion LanguageMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
JRSchaefer

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
gdemaria

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
_agx_

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JRSchaefer

ASKER
Both options work, thanks for your help!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck