CODEWITHSUNDEEP
sqlsql-server
khadi8
khadi8

Reputation: 13

Search Duplicate rows

I want to check if there are duplicates in my table ParticipantsDebtor, duplication is compared to Name, ParticipantsCode and DateOfBirth the Name and ParticipantsCode field is in the ParticipantsDebtor table and DateOfBirth is in ParticipantDebtorDetail table, my request turns into the void I do not know if this is correct but very long or it is false

Table ParticipantsDebtor has:

Table ParticipantDebtorDetail has:

Query:

SELECT a.ParticipantCode,
       a.Name,
       COUNT(a.DebtorId) AS DuplicateNumber,
       b.DateOfBirth
FROM ParticipantDebtors a WITH (NOLOCK),
     crem.ParticipantDebtorDetail b WITH (NOLOCK)
WHERE a.DebtorId <> b.DebtorId
GROUP BY a.ParticipantCode,
         a.Name,
         b.DateOfBirth
HAVING COUNT(a.DebtorId) > 1

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Not correct. You need join keys . . . Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax:

SELECT d.ParticipantCode, d.Name, dd.DateOfBirth, COUNT(*) as NumDups
FROM ParticipantDebtors d with(nolock) JOIN
     crem.ParticipantDebtorDetail dd  with(nolock)
     ON d.DebtorId = dd.DebtorId
GROUP BY d.ParticipantCode, d.Name, dd.DateOfBirth
HAVING COUNT(*) > 1;

If a single debtor can appear in either table more than once, then you can replace the HAVING condition and calculation of NumDups with:

HAVING COUNT(DISTINCT d.DebtorID) > 1

Upvotes: 1

Related Questions