dali1985 dali1985 - 27 days ago 6
SQL Question

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?

Answer

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
Comments