CODEWITHSUNDEEP
phpsqlcodeigniter
user7408149
user7408149

Reputation:

Don't want list of user which are blocked by user in list?

Table : user 
Id name
1   a
2   b
3   c
4   d

Table : block_user
id block_by block
1   1         2

when i retrieve list for user 1 at that time i don't want user 2 in list but i stuck at query: i want user 3 , 4 in place of it return user 2 which is blocked

my sql fiddle Fiddle here and my current query 

Select u.* from user u JOIN block_user bu on bu.block_by = u.Id or bu.block = u.Id where u.Id != 1 GROUP BY u.Id

Upvotes: 2

Views: 88

Answers (4)

Rajat Masih
Rajat Masih

Reputation: 583

use can use nesting query with where not in 

select * from user where id  NOT IN (select block from block_user)

Upvotes: -1

Jagdish Chaudhary
Jagdish Chaudhary

Reputation: 287

you can also use this is very sort you don'have to join table instead using where not in 

select * from user where id != 1 and id NOT IN (select block from block_user WHERE block_user.block_by = 1)

Upvotes: 2

Amit Gaud
Amit Gaud

Reputation: 766

Use this hope it will help you

 select * from user where id = 1 AND id NOT IN 
(select GROUP_CONCAT(block) from block_user where block_by = 1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you want not exists or left join. The correct logic is:

select u.*
from user u
where u.id <> 1 and
      not exists (select 1
                  from block_users bu
                  where bu.block_by = 1 and bu.block = u.id
                 );

Upvotes: 1

Related Questions