Using MSSQL to define distinct cross-column relationships...
Someone asked me this morning how they could determine distinct relationships. They wanted to be able to retrieve all the distinct rows where someone had defined another user as a friend or any users who had defined them as a friend. The table schema looked like this:
| friend_user1 | friend_user2 |
| 1 | 10 |
| 2 | 6 |
| 10 | 1 |
| 6 | 1 |
| 2 | 1 |
| 10 | 2 |
As you can see friend 1 is pretty popular amongst other users. However, he only has 1 person defined as friend of his—friend 10. What we need to do is to find all distinct cross-column relationships in the table—which means only one row should be returned for the relationship between friend 1 and friend 10.