Consider a SQL Server 2005 database with a bunch of data on a lot of people, a large chunk of whom are married. To track relationships among people, a Relationship table exists that serves to link the ID of one person to the ID of another. A Relationship Type on each Relationship record serves to indicates the type of relationship exists between the two people in question (duh): "is married to", "is son of", "is daughter of", etc.
For married people, there are TWO relationship records in the table--one showing Person-A is married to Person-B and another record showing that Person-B is married to Person-A. For a marriage, it would be considered an error to not have both records, though there are some un-reciprocated records in the DB. (This is an inherited design and I do not have the freedom to change this approach.)
RelID Person1 Person2 RelationshipType
-----------------------------------------
1 1001 1010 Married //Reciprocated by RelID 4
2 1002 1011 Married //Reciprocated by RelID 5
3 1003 1012 Married //Reciprocated by RelID 6
4 1010 1001 Married //Reciprocated by RelID 1
5 1011 1002 Married //Reciprocated by RelID 2
6 1012 1003 Married //Reciprocated by RelID 3
7 1004 1013 Married //Not properly reciprocated
What I need to do is extract unique married couples from the table.
I created a function called GetOrderedPair(val1,val2) which takes 2 Person IDs and returns a comma separated string value of both values in sequential order. This way, it always returns the same string value regardless of whether I get, referring to above example data, Person1 and Person2 from record with RelID of 1 or RelID of 4 (a reciprocating pair of records).
I can't help but think that there is a better way that eludes me. Is the use of this function the best approach? What else might you recommend?
Thanks, of course, in advance. :-)