Query To Retrieve Duplicate Records

• Jerish

  SSC Veteran

  Points: 267

  More actions

  April 20, 2002 at 6:48 am

  #104898

  Hi Guys,

  I am using SQL Server 2000 as my database. I need a query for the following purpose.

  I have two tables Emp and Emp_Name .

  Emp Table has fields like Emp_id (integer),DOB(date),PhoneNo(varchar),.......

  Emp_Name has fields like Emp_id (integer),lastname,firstname,middlename,.....

  I don't have any Primary key and Foreign Key relation ship between these two tables.

  I want to get all the Duplicate Employee related records . ie , I want to get employee

  details who has same first name , last name , middle name and DOB.

  Will a single query do the above job ? please give a exact query for the above one.

  Thanks In Advance.

  Jerish

  dess@satyam.net.in

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  April 21, 2002 at 4:10 pm

  #427562

  select b.firstname, b.lastname, b.middlename, a.dob, count(*)

  from emp a

  inner join emp b

  on a.emp_id = b.emp_id

  group by b.firstname, b.lastname, b.middlename, a.dob

  having count(*) > 1

  Steve Jones

  steve@dkranch.net