CODEWITHSUNDEEP
t-sql
user1627800
user1627800

Reputation: 3

How to pull out the employees who have 'first name,lastname and date of birth' as same from Employee table

I need to pull out the records whose First name,lastname and date of birth are of same. Please find the below example.

Employeeid   firstname lastname DOB
00010         ravi      sagi     22/01/1990
00035         ravi      sagi     22/01/1990
00060         vasanth   guptha   20/01/1987
00115         vasanth   guptha   20/01/1987

Can you please help in writing the query.

Upvotes: 0

Views: 1487

Answers (2)

Taryn
Taryn

Reputation: 247850

You can JOIN the table to itself comparing the firstname, lastname and DOB to make sure they are the same value and then that the employeeid is not the same:

select * 
from yourtable t1
inner join yourtable t2
  on t1.firstname = t2.firstname
  and t1.lastname = t2.lastname
  and t1.dob = t2.dob
  and t1.empid != t2.empid

the above query could display duplicate records, so you could use the following (see SQL Fiddle with Demo):

select DISTINCT t1.empid,
  t1.firstname,
  t1.lastname,
  t1.DOB
from yourtable t1
inner join yourtable t2
  on t1.firstname = t2.firstname
  and t1.lastname = t2.lastname
  and t1.dob = t2.dob
  and t1.empid != t2.empid

Or you can use EXISTS (See SQL Fiddle with Demo):

select t1.empid,
  t1.firstname,
  t1.lastname,
  t1.DOB
from yourtable t1
where exists (SELECT *
              FROM yourtable t2
              WHERE t1.firstname = t2.firstname
                and t1.lastname = t2.lastname
                and t1.dob = t2.dob
                and t1.empid != t2.empid)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Try this:

select *
from
  (
    select *,
           count(*) over(partition by firstname, lastname, DOB) as CC
    from YourTable
  ) as T
where T.CC > 1

Upvotes: 4

Related Questions