-1

I'm trying to write a SQL query that will give me a list of duplicate persons in a database. I can not create a new table with this query.

I'm trying to find where persons have matching first name, last name and dates of birth.

The names are held in a table persons and dates of birth are stored in personsInfo linked with a matching id.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Nathaniel Hodges
  • 13
  • 2
  • 1
    You better include table schema and sample data. – dario Aug 19 '15 at 17:48
  • possible duplicate of [Select statement to find duplicates on certain fields](http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields) – Bulat Aug 19 '15 at 18:50

1 Answers1

1

You should be able to do this with:

select p.id, p.firstName, p.lastName, I.DoB, count(*)
from persons as p
join personsInfo as I
on p.ID = i.PersonsID
group by p.firstname, p.lastName, I.DoB
having count(*) > 1

Found the answer by searching "select duplicates sql" and looking at this question and answer.

Community
  • 1
  • 1
Colin
  • 91
  • 7