CODEWITHSUNDEEP
phpmysqlsql
Mensur
Mensur

Reputation: 475

Exclude results in Mysql, NOT IN

I need help to write an SQL but i have no idea in this case how. Tried many different options but nonthing worked.

I have 2 tables in my mysql database

users (id, name, lastname)
blocked_users (id, user_id, blocked_id)

users table
id  name    lastname
1   nick    james
2   james   dean
3   mike    bendon

blocked_users table
id  user_id     blocked_id
1   2           1
2   2           3

Example:

What i would like to do is display all users in users table but exclude in this case those which are blocked by user_id 2.

So if i'm example logged in as Mike Bendon (id 3 in users table) i should not be able to se james dean (id 2 in users table) because he has blocked me.

Upvotes: 1

Views: 504

Answers (2)

SubRed
SubRed

Reputation: 3187

Try this query:

SELECT * 
FROM   users 
WHERE  id NOT IN (
       SELECT user_id 
       FROM   blocked_users 
       WHERE  blocked_id=$current_user_id);

Upvotes: 0

Cynical
Cynical

Reputation: 9588

Not sure of how to run that with php, but I think this SQL should do what you asked:

SELECT *
FROM users
WHERE id NOT IN
(SELECT user_id FROM blocked_users WHERE blocked_id = $logged_id);

Edit after comment: Sorry, I misread the id column name. Should be fixed now.

Upvotes: 3

Related Questions