SQL join help for friend list

I have three database tables: 开发者_如何转开发users, user_profiles and friends:

users

• id
• username
• password

user_profiles

• id
• user_id
• full_name

friends

• id
• usera_id
• userb_id

What would be a query which finds the friends list of any user and also joins the table users and user_profiles to get the profile and user information of that friend?

Use:

SELECT f.username,
       up.*
  FROM USERS f
  JOIN USER_PROFILES up ON up.user_id = f.id
  JOIN FRIENDS fr ON fr.userb_id = f.id
  JOIN USERS u ON u.id = fr.usera_id
 WHERE u.username = ?

...assuming userb_id is the friend id.

This may not be the best way to do it, but this felt like the logical way:

select a.id , a.friend_id ,
Users.username
from
 ( SELECT id , IF(usera_id = 1, userb_id , usera_id) friend_id
 FROM friends
 where usera_id = 1 OR userb_id = 1 ) a
left join Users on a.friend_id = Users.id

this uses a mySQL function so probably wont work in Oracle/MSSQL

Falling back on bad habits (not using JOIN notation in the FROM clause):

SELECT a.id, a.username, a.full_name,
       b.id, b.username, b.full_name
  FROM friends AS f, users AS ua, users AS ub,
       user_profiles AS a, user_profiles AS b
 WHERE f.usera_id = ua.id
   AND f.userb_id = ub.id
   AND a.user_id  = ua.id
   AND b.user_id  = ub.id

The key point is using table aliases (all those 'AS' clauses) and referencing the same table more than once when necessary.

Someone could write this with JOIN instead.

Some modification to eugene y's answer, will this work?

SELECT * FROM users u
JOIN friends f ON (f.userb_id = u.id OR f.usera_id = u.id)
JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = ?