Link to home
Start Free TrialLog in
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!
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JRSchaefer
JRSchaefer

ASKER

Both options work, thanks for your help!