Tuesday, April 26, 2011

How to find broken 2-way relationships

This is not advice on how to interact with people. I'm not the best person to ask for that.
===
I have a table to represent friendships. In this table there must be two rows for each friendship: one for User A->User B, and one for User B->User A.

There are some friendships where the second row is missing.
How do I find them?

Here are the pertinent tables and columns:
Table: User
Columns: userID

Table: UserFriend
Columns: fromUserID, toUserID
===
Solution:

select UF.*
from UserFriend UF with (nolock)
left outer join UserFriend UF2 with (nolock) on
UF.fromUserID = UF2.toUserID and
UF.toUserID = UF2.fromUserID
where UF2.fromUserID is null