Милош Поповић Милош Поповић - 6 months ago 9
SQL Question

Get all users who is not friend with login user

i have a little problem with MySql query. I have two tables:

Table name "users"
id, profilephoto, sex, name, lastname

Table name "friends"
id, idSender, idReceiver


idSender and idReceiver is value of id users who send friend request, and who get friend request.

Now i need get all users who is not friend with some login user (for example - User ID - 12)

I make this query:

SELECT DISTINCT users.id, users.profilephoto,users.sex,users.name,users.lastname FROM users INNER JOIN friends ON users.id=friends.idSender WHERE (friends.idSender!=12 OR friends.idReceiver!=12)


And I get wrong informations.

If someone have any idea, I will be thankful.

Simple data from table>

Table Users:

id | profilephoto | sex | name | lastname

1 | image1.jpg | 1 | John | Snow

2 | image2.jpg | 2 | Lisa | Test

3 | image3.jpg | 1 | Patric | Test

4 | image4.jpg | 2 | Elizabet | Test



Table friends:

id | idReceiver | idSender

1 | 1 | 2 // Lisa send friend request to John

2 | 2 | 3 // Patric send friend request to Lisa


For Lisa I want to display only Elizabet

For Elizabet I want to display Lisa, John and Patric

For John I need Patric and Elizabet

For Patric I need John and Elizabet

Answer

You need to make left join

SELECT * from users u 
LEFT JOIN friends f ON u.id = f.idSender OR u.id = f.idReceiver
WHERE u.id = 12 
AND f.idSender IS NULL 
OR f.idReciever IS NULL 

Hope it helps.