SQL join help for friend list

Question

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?

Answer 1

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.

Answer 2

Try something like this:

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

You may want to list column names explicitly.

Answer 3

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.

Answer 4

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 = ?

Answer 5

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