"users and their friends" query

Question

users

• id
• username

friends

• user_id (invitor)
• friend_id (that get invitation)
• accepted

Now If user #1 invites user #2 to be a friend and #2 acceptes, I want user 2 displayed on user 1's site and user 1 on user 2's site

But i cant figure out how the query for this should look. Been scratching my head for a long while

help would be much appriciated

Answer 1

SELECT users.username FROM users, friends WHERE friends.accepted = 1 AND friends.user_id = ... AND friends.friend_id = users.id ?

Answer 2

Suppose the id of the user you are showing the page from is 5:

for the friends the user invited:

SELECT username 
FROM users, friends
WHERE id = friend_id AND user_id = 5 AND accepted = 1

for the friends which send the invitation to the user

SELECT username 
FROM users, friends 
WHERE id = user_id AND friend_id = 5 AND accepted = 1

In one query:

SELECT username 
FROM users, friends 
WHERE 
  (      
    (id = friend_id AND user_id = 5)
    OR
    (id = user_id AND friend_id = 5)
  )
  AND accepted = 1

Answer 3

If I understand you correctly I think this will work for you...

For user 2 to appear on user 1 page...

SELECT U.ID, U.UserName
FROM Friends F
INNER JOIN Users U ON F.Friend_ID = U.ID
WHERE
  F.User_ID = [User 1 ID]
  AND F.Accepted = [True|1]

For user 1 to appear on user 2 page...

SELECT U.ID, U.UserName
FROM Friends F
INNER JOIN Users U ON F.User_ID = U.ID
WHERE
  F.Friend_ID = [User 2 ID]
  AND F.Accepted = [True|1]

You'll have to replace the bracketed values with what applies in your database...