CODEWITHSUNDEEP
phpmysqlsql
Giorgos Karyofyllis
Giorgos Karyofyllis

Reputation: 117

Select Users that are not blocked - SQL

I have a table users:

And another table blocked_users:

When a user blocks another, the add their id to the blocked_users.user_id and the user they block in blocked_users.user_blocked_id.

I want to select all the users from users table where my user.id does not exits neither in blocked_users.user_id (Users that I have blocked) nor blocked_users.user_block_id (Someone else has blocked me). So none of them can see other's information.

SELECT        a.*
FROM          users a
LEFT JOIN     blocked_users b
ON            b.user_id = a.id
AND           b.user_blocked_id = a.id
WHERE NOT     b.user_id = '$id'
AND NOT       b.user_blocked_id = '$id';

Im Stuck on the query!

Upvotes: 1

Views: 1040

Answers (2)

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

If you want to select all of the users from users table, then use the following code:

select u.*
  from users u
 where not exists (select 1
                     from blocked_users bu
                    where bu.user_id = u.id) 
   and not exists (select 1
                     from blocked_users bu
                    where bu.user_blocked_id = u.id);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271091

Use not exists:

select u.*
from users u
where not exists (select 1
                  from blocked_users bu
                  where bu.user_id = u.id and bu.user_blocked_id = ?
                 ) and
      not exists (select 1
                  from blocked_users bu
                  where bu.user_blocked_id = u.id and bu.user_id = ?
                 );

Note the use of ?. This is for passing in parameters into the query. This is much better than munging the query with a string value.

Upvotes: 1

Related Questions