CODEWITHSUNDEEP
mysql
kelrob-dev
kelrob-dev

Reputation: 101

Sql to get all users that does not have relationship with a particular user

I am having a serious issue at getting results of users that do not have relationship with me on the friends table. And I want the user returned that has privacy = 0. Please bear in mind, I am Id of number 3

So I have Two Tables (Users Table and Friends Table).

Users Table

id| username| country | privacy
1 | Everest | Austria | 0
2 | Mark    | Sweden  | 0
3 | Precious| Norway  | 1
4 | Daniel  | Ghana   | 1
5 | Jim     | Greece  | 1
6 | Ayo     | Brazil  | 0

Friends Table

id| sender_id| receiver_id| accepted
1 | 1        | 3          | 0
2 | 3        | 4          | 0

I will like to have the expected Result Expected Result

id| username| country | privacy
2 | Mark    | Sweden  | 0
6 | Ayo     | Brazil  | 0

Upvotes: 0

Views: 407

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272086

I think this should do it:

SELECT *
FROM users
WHERE privacy = 0
AND id <> 3
AND id NOT IN (
    SELECT receiver_id FROM friends WHERE sender_id   = 3
    UNION ALL
    SELECT sender_id   FROM friends WHERE receiver_id = 3
)

It assumes that sender_id and receiver_id cannot contain NULL.

Upvotes: 1

Related Questions