Friend relationship query

Question

I have the following tables:

USERS table

• id
• username
• email
• password
• status

FRIENDS table

• id
• user_id
• friend_id
• created

To find a users friends, I use:

SELECT f.friend_id, 
       u.username
  FROM friends f
  JOIN friends m ON m.user_id = f.friend_id 
                AND m.friend_id = f.user_id
  JOIN users u ON u.user_id = f.friend_id
 WHERE f.user_id = 1234

...which works great.

ISSUE

I can't figure out a similar query in the other way though, find all the users that aren't friends yet. It would have to be something along the lines of, get all the friends, get all the users, remove all users that are the friends to leave the remainder?

Answer 1

    SELECT u1.username, u2.username AS not_friend
      FROM users u1
      JOIN users u2 on u1.user_id != u2.user_id
 LEFT JOIN friends f1 on u1.user_id = f1.user_id
                     and u2.user_id = f1.friend_id
 LEFT JOIN friends f2 on u2.user_id = f2.user_id 
                     and u1.user_id = f2.friend_id
     WHERE u1.user_id = 1234
       AND (f1.user_id IS NULL OR f2.user_id IS NULL)

for friends:

SELECT u1.username, u2.username AS friend
  FROM users u1
  JOIN users u2 on u1.user_id != u2.user_id
  JOIN friends f1 on u1.user_id = f1.user_id
                 and u2.user_id = f1.friend_id
  JOIN friends f2 on u2.user_id = f2.user_id 
                 and u1.user_id = f2.friend_id
 WHERE u1.user_id = 1234