CODEWITHSUNDEEP
mysqlsql
Madis Pukkonen
Madis Pukkonen

Reputation: 1315

MySQL - exclude all blocked users from the results


I'm a little stuck with a MySQL query:

I have table users

user_id|language|name
1      |en      |Mike
2      |en      |John
3      |en      |Jesse
4      |de      |Wolfgang (Me)

and table users_blocked

user_id|blocked_user_id
1      |4
4      |1
1      |3
3      |1
4      |2

(where user_id has blocked blocked_user_id)

I want to find all users (excluding myself) who speaks English (en), I haven't blocked them, they haven't blocked me. It should return only user with ID 3 from the example above.

I'm building a language partners search where it's excluding all blocked users (both ways) from the results.

Upvotes: 1

Views: 587

Answers (2)

AgRizzo
AgRizzo

Reputation: 5271

SELECT users.user_id, users.language, users.name
FROM users
WHERE users.language = 'EN'
  AND NOT EXISTS(SELECT * 
                 FROM users_blocked
                 WHERE (users_blocked.user_id = 4
                        AND users_blocked.blocked = users.user_id)
                   OR (users_blocked.blocked = 4
                        AND users_blocked.user_id = users.user_id)
                 )

Upvotes: 1

Ben
Ben

Reputation: 46

Here, give this a go:

select * from users where user_id <> 4 and language = 'en'
    and user_id not in(select blocked_user_id from users_blocked where user_id = 4)
    and user_id not in(select user_id from users_blocked where blocked_user_id = 4)

Demo here: http://sqlfiddle.com/#!2/b22a4/2

Upvotes: 3

Related Questions