SQL Server Performance Forum – Threads Archive

Searching a table again..

Can someone please help me with this problem. I have a table with different information about persons (personId, name, dateOfBirth, mother’s name, father’s name..) I want to write a SQL query that searches throuh the table and returns information about registered persons that are twins (have the same mother’s name, father’s name and dateOfBirth). The returned information I want to have like this:
Name1, Name2, dateOfBirth, mother’s name, father’s name [?][?][?]

select t1.name, t2.name, t1,dateofbirth, t1.mother, t1.father
from person t1
inner join person t2
on t1.dateofbirth = t2.dateofbirth
and t1.mother = t2.mother
and t1.father = t2.father
where t1.name <> t2.name
and t1.id < t2.id This query will also show triplets, but as three pairs of twins – #1 and #2, #1 and #3, and #2 and #3. As you get into quadruplets and upwards, the number of pairs increases exponentially. To exclude triplets, etc. – add this … and not exists
(select tx.* from person tx
where tx.dateofbirth = t1.dateofbirth and tx.mother = t1.mother
and tx.father = t1.father and tx.name <> t2.name
and tx.id > t2.id) etc., etc.

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Thanks a lot. But is it possible to get the returned information in just one line? Like this: Name1, Name2, Date of birth, Mother’s name, Father’s name [?]

You haven’t actually looked at the results, have you? Look at the table aliases in the column list – t1 and t2 refer to the same underlying table, but in a self-join – like the term implies: you join a table onto itself. The data named t1 comes from the same table as the data named t2, and it is combined the same way as data from two different tables would be combined. So data is read from t1 and combined with the data read from t2, based on the join expression and the where clause. The t1.name and t2.name columns in the resultset give you the names of a pair of twins. The parent names and DOB can be read from either t1 or t2, as they are identical. For triplets, you will find three rows combining the first and second twins, the first and third twins, and the second and third twins.

By the way, not sure how this is handled in your country, but twins can have a different DOB (like before and after midnight). You’ll need to check what the official policy is, but in such a case they could register twins with a different DOB. In that case you would need either a cross-reference table to establish twins, triplets, etc., or an additional check to see that the DOBs are no more than 1 day apart.

[:I] Im sorry. I left out the t2.name in the select-part. Now it works perfectly!! Thanks a lot!!!!! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

]]>