There 2 tables emailinfo(id,email) and keywordinfo(id,keyword). emailinfo contains 80,000 rows and keywordinfo contains 2000 rows.
I want emails from emailinfo table which does not contains keywords from keywordinfo table. eg. SELECT email FROM emailinfo WHERE email not like '%yahoo%' AND email not like '%gmail%'
but this is hardcoded example.and i kept these keywords in keywordinfo table.
In sql procedure i tried for following query,
SELECT EMAIL.email FROM emailinfo EMAIL WHERE ( not exists( Select keyword from keywordinfo where EMAIL.email like '%'+ keyword +'%' )) which take 2 minutes to execute.
I want query like following
SELECT EMAIL.email FROM emailinfo EMAIL where EMAIL.email not like '%' ( SELECT keyword from keywordinfo ) '%'
i know above subquery returns multple rows,but i want something like that.
i have googled many examples but they failed to satisfying my problem
Please help me to write NOT LIKE subquery with values pulled from another table?Thanks in advance.
asked May 17, 2012 at 09:43 AM in Default
I can understand what is desired. You need to know any new keywords missing in your keyword table. But I guess this should be done at the time of insert/update if to be handled efficiently. Otherwise, should be done as a nightly job.
Extract the keyword by finding the string between @ and first . DECLARE @email VARCHAR(50)
A very good article by Aaron Bertrand is here
I have simulated the exact case on my machine but without the use of NOT LIKE and it worked great
WHERE K.[id] IS NULL