Sample/Example needed for a table/field setup
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 开发者_JS百科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.
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)
精彩评论