Милош Поповић Милош Поповић - 3 years ago 83
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download