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

emails table:

blockedSender table:

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?

CodePudding user response：

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   '%'
)

CodePudding user response：

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