CODEWITHSUNDEEP
sql
dali1985
dali1985

Reputation: 3333

Query to find pairs of employees who have the same birthdate

I have a table EMPLOYEE with 100 people. I would like to write a query to find pairs of employees who have the same BIRTHDATE.

The result should return EMPNO, LASTNAME and BIRTHDATE for each employee in the pair (a 6-column result table).

I thought something like that

SELECT  t1.EmpNo
       ,t1.LastName
       ,t1.BirthDate
        ,t2.EmpNo
        ,t2.LastName
        ,t2.BirthDate
  FROM Employee t1
  INNER JOIN (
  SELECT EmpNo
        ,LastName
        ,BirthDate
  FROM Employee ) t2 ON t2.BirthDate = t1.BirthDate 
  WHERE t2.EmpNo != t1.EmpNo

Do you think it is correct?

Upvotes: 3

Views: 38850

Answers (4)

Kiritharan Nithya
Kiritharan Nithya

Reputation: 1

Here is my answer

Select name, date From employees Group by date of birth Having count(*)>1;

Upvotes: -1

Karol Borkowski
Karol Borkowski

Reputation: 742

I would also add the condition that t1.EmpNo<t1.EmpNO to avoid repetions.

Upvotes: 0

anothershrubery
anothershrubery

Reputation: 21023

I would just do:

SELECT t1.EmpNo, 
       t1.LastName, 
       t1.BirthDate, 
       t2.EmpNo, 
       t2.LastName, 
       t2.Birthdate
FROM Employee t1, Employee t2
WHERE t1.BirthDate = t2.Birthdate
AND t1.EmpNo <> t2.EmpNo

Upvotes: 6

user617850
user617850

Reputation: 35

It looks correct but I would remove the second select.. sub query

SELECT 
t1.EmpNo ,t1.LastName ,t1.BirthDate ,t2.EmpNo ,t2.LastName ,t2.BirthDate 
FROM Employee t1 
INNER JOIN Employee t2 ON t2.BirthDate = t1.BirthDate 
WHERE t2.EmpNo != t1.EmpNo

Upvotes: 1

Related Questions