query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query question

Author

Topic

theo1985
Starting Member

3 Posts

Posted - 2011-02-16 : 19:38:17

Hi all!
I would like your help!
I have a table EMPLOYEE!I would like to write a query to find pairs of employees who have the same BIRTHDATE.
The result will Return EMPNO, LASTNAME and BIRTHDATE for each employee in the pair (a 6-column result table).

for example
3 Johnson 13/6/78 7 Patrick 13/6/78
9 Carter 10/4/89 1 Bryant 10/4/89

I am thinking something like that

Select t1.empno,t1.lastname,t1.birthdate,t2.empno,t2.lastname,t2.birthname
from employee t1 join employee t2
where t1.birthdate<>t2.birthdate

Do you know exactly what I must write?

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-02-16 : 20:36:57

Is this homework or a real query? If it is real, what if there are 3 people or more with the same birthday? This will get you there, but you need to consider what to do with the reversed duplicates.


DECLARE @A TABLE(
EmpNo INT, FirstNAme VARCHAR(50), LastName VARCHAR(50), BirthDate DATE
)
INSERT @A 
SELECT 1, 'Bill', 'Smith', '19740103' UNION ALL
SELECT 2, 'Mary', 'Jones', '19600518' UNION ALL
SELECT 3, 'Marvin', 'Barnes', '19740103' UNION ALL
SELECT 4, 'Bart', 'York', '19600518'


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

===
http://www.ElementalSQL.com/

theo1985
Starting Member

3 Posts

Posted - 2011-02-17 : 04:26:57

Thank you very much for your help.
If I would like to find pairs of employees who have the same BIRTHDAY date each year how can i do this? Again I want to return EMPNO, LASTNAME and BIRTHDATE for each employee in the pair.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources