finding duplicates

Question

I'm trying to get duplicate entries in a table and I need to match the id field with another table. I found duplicates with a query but couldn't get the id as well, so I found this on a web site to retrieve duplicates with id (a modified version of the query I was using). It doesn't work though and I'm not sure why. I get the error: Incorrect syntax near ','. I can't see where it's complaining about. It's an MSSQL database.

SELECT id, Lname, Fname
   FROM clients
   WHERE (Lname, Fname) IN
   (SELECT Lname, Fname
       FROM clients
       GROUP BY Lname, Fname
       HAVING COUNT(*) > 1)
   ORDER BY Lname, Fname

Can anyone help?

JR__Bob__Dobbs-qSBHQ2 · Accepted Answer

The problem is this line: "WHERE (Lname, Fname) IN". You could put a single column here, but not multiple.

Assuming that a duplicate is defined as multiple rows having the same values for both the Lname, Fname fields try the query below. Note that I have not tested this query.

SELECT
    C.id,
    C.Lname,
    C.Fname

FROM
    -- get names and count each Lname, Fname combination occurs
    (
    SELECT Lname, Fname, COUNT(*) AS Name_Count
    FROM clients
    GROUP BY Lname, Fname
    ) AS Q

    -- join on original clients table to get id field
    INNER JOIN clients AS C
        ON ( Q.Lname = C.Lname AND Q.Fname = C.Fname )

-- only include names which are duplicated
WHERE Q.Name_Count > 1

Message was edited by: JR "Bob" Dobbs Corrected join in query.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded