CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Samuel
Samuel

Reputation: 2019

Sql Query with NOT LIKE IN WHERE CLAUSE

I have two tables emails and blockedSender I am trying to remove blocked sender from the email query. e.g.

emails table:

user_id from_address
1 name-1 <[email protected]>
2 name-2 <[email protected]>

blockedSender table:

blocked_address
[email protected]

Here I want to return all elements from the emails table where the from_address does not do a LIKE match with entries in the blocked address. Something similar to

select email_id, from_address 
from emails e 
where from_address not like in (select '%' + blocked_address + '%' 
                                from blockSenders)

which does not work because of incorrect syntax. Is there any way to do this?

Upvotes: 1

Views: 416

Answers (2)

Stu
Stu

Reputation: 32589

You can express this using not exists

select e.email_id, e.from_address
from emails e 
where not exists (
    select * from blockedSenders b 
    where e.from_address like '%' + b.blocked_address + '%'
)

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

It could be rewritten as JOIN:

select e.email_id, e.from_address 
from emails e
left join blockSenders b
  ON e.from_address LIKE '%' + b.blocked_address + '%' 
where b.blocked_address IS NULL;

db<>fiddle demo

Upvotes: 2

Related Questions