OK, so I have a table that looks like this:
FriendId (int) | UserId (int)
This basically provides a way to track the relationship between a user and their friends... like myspace or friendster.
Now each time I add a friend i only add one entry. Say the user id = 9 and friend id = 7, so it would look like this:
So now i want to display the users friends. their friends can be either added by the friend or the user, there my query needed to look like this:
Here is a simplified version:
SELECT * from Friend A
left JOIN user B ON B.UserId = A.FriendId
LEFT JOIN Image C on B.MainImgId = C.ImageId
WHERE A.UserId = @userid OR A.FriendID = @UserID
This is problimatic, as I'm sure you can see. The problem is that
1) it pulls duplicate records
2) it shows the user as a friend because that satifies it conditions.
Can you offer a better way to write this simple query??