Sample/Example needed for a table/field setup

Question

Can someone explain the statement below to me with a working sample/example. thanks in advance.

You can not create duplicate fields, but simply add a single extra field, "coupleId", which would have a unique id for each couple; and two rows (one for each person) per couple; then JOIN the table against itself with a constraint like a.coupleId = b.coupleId AND a.id <> b.id so that you can condense the data into a single result row for a given couple.

Answer 1

Given the table Person, as follows:

Person
==========
PersonID (int, autoincrement)
CoupleID (nullable int)
Name (nullable varchar(50))

Insert some rows:

insert into Person (CoupleID, Name) values (1, 'John')
insert into Person (CoupleID, Name) values (1, 'Mary')
insert into Person (CoupleID, Name) values (null, 'Sue')

Now you can find people that make up a couple with the following query:

select p1.CoupleID, p1.PersonID as Person1ID, p1.Name as Person1Name, p2.PersonID as Person2ID, p2.Name as Person2Name 
from Person p1 
inner join Person p2 on p1.CoupleID = p2.CoupleID 
where p1.PersonID <> p2.PersonID 
order by p1.PersonID

Output:

PersonID    CoupleID    Name                                               PersonID    CoupleID    Name
----------- ----------- -------------------------------------------------- ----------- ----------- --------------------------------------------------
2           1           Mary                                               1           1           John
1           1           John                                               2           1           Mary

(2 row(s) affected)