Search Duplicate rows

Question

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:

DebtorId
Name
ParticipantsCode

Table ParticipantDebtorDetail has:

DebtorId
DateOfBirth

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

Gordon Linoff · Accepted Answer · 2016-03-22 10:49:18Z

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

I get wrong result, NumDups is always between 2 and 3 this is wrong in my table why ?
@khadi8 . . . I can't answer that without more information. Can you set up a SQL Fiddle with sample data that shows the problem.

Collectives™ on Stack Overflow

Search Duplicate rows

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related