Click here to Skip to main content
15,608,053 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Expert,

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.

And my query is like following.

SELECT FROM emailinfo EMAIL    WHERE   (  not exists( Select keyword from keywordinfo where  like '%'+ keyword +'%' ))

but it takes 3 minutes to execute .

please help me to slove this problem.

Thanks in advance.

I want emails from emailinfo table which does not contains keywords from keywordinfo table.

SELECT  [email]
FROM emailinfo
WHERE NOT [email] IN(SELECT [keyword] AS [email]
                    FROM keywordinfo
                    WHERE [keyword] like '%'+ keyword +'%' )
Share this answer
udusat13 14-May-12 0:40am    
Thanks for replying me,
but your above query retrives all the emails from emailinfo table.
i have added yahoo keyword in keywordinfo table,but still it retrieveing email that contains yahoo keyword.

Please help me.
WITH keywords AS

WHERE NOT LIKE '%' + keywords.keyword + '%'
Share this answer
Maciej Los 11-May-12 8:50am    
Please, do not multiply answers. Please, move it into previous answer.
phil.o 11-May-12 8:53am    
It is a radically different solution as the 1st one, so no, sorry, I won't move it.
udusat13 14-May-12 0:41am    
Thanks for solution.

but it gives error like

The multi-part identifier "" could not be bound.
The multi-part identifier "keywords.keyword" could not be bound.
phil.o 14-May-12 2:33am    
Hi, I had forgotten the FROM part of the query.
Please try again with it included.
udusat13 14-May-12 2:40am    
thanks but i am confused,
because i am new to sql server
That query will always take a long time. You need a different design for your solution: When a new email arrives, check it for the keywords, and add new entries to a linking table containing emailid and keywordid. The time required for doing that extra step at email arrival does not matter. When you search later on, no full text search is required anymore.
Share this answer
Make sure there is an index on your email column.
Share this answer
udusat13 11-May-12 8:14am    
Thanks for replying me.
I have already indexed email column from emailinfo table and keyword column from keyword info table.

Please check the query is right or not?
Thanks again.
phil.o 11-May-12 8:46am    
Your query does not seem really correct (syntacticaly speaking) to me : in your nested query (SELECT keyword FROM keywordinfo WHERE emailinfo...), you query the keywordinfo table but take a condition on the emailinfo table.
I'll try to post a solution using a CTE as soon as I can.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900