I am fully able to get all of the logged in users current friends, which I do with this query here -
SELECT users.*
FROM users
LEFT JOIN friends
ON users.id = friends.friend_id
WHERE friends.user_id = $user_logged_in_id
But I want to do the opposite, and I can't figure it out.
I am trying to do friends logic.
I need to get all the users who the currently logged in user is not already friends with
I have a users table and a friends table to act as a many to many relationship between users and their friends. The keys in the friends table are user_id friend_id.
To illustrate that someone is friends with another user I put (say the logged in user id is 3, and friends user id is 6) user_id = 3
and friend_id = 6
as one row, and then put another row with the id's flipped to illustrate the friendship the other way, user_id = 6
and friend_id = 3
.
users table
|---------------------|------------------|
| id | username |
|---------------------|------------------|
| 1 | sonja12 |
|---------------------|------------------|
| 2 | dorris32 |
|---------------------|------------------|
| 3 | billy32 |
|---------------------|------------------|
| 4 | micha97 |
|---------------------|------------------|
| 5 | travis841 |
|---------------------|------------------|
| 6 | johnny28 |
|---------------------|------------------|
friends table
|---------------------|------------------|------------------|
| id | user_id | friend_id |
|---------------------|------------------|------------------|
| 1 | 3 | 6 |
|---------------------|------------------|------------------|
| 2 | 6 | 3 |
|---------------------|------------------|------------------|
EDIT:
I think I have narrowed down my question a bit to be more concise.
I want to get all users from the users table where their id does not appear as a friend id beside the user_id of the current logged in user in the friends table.
You're thinking of the problem in a strange way. It might help to think of it as an intersection of sets you need to create as opposed to a set that exists in the absence of an id.
SELECT id from users WHERE id != $logged_in_user AND id NOT IN ( SELECT friend_id from friends where user_id=$logged_in_user)