Click here to Skip to main content
15,569,840 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.

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



but it takes 3 minutes to execute .

please help me to slove this problem.

Thanks in advance.
Posted

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

SQL
SELECT  [email]
FROM emailinfo
WHERE NOT [email] IN(SELECT [keyword] AS [email]
                    FROM keywordinfo
                    WHERE [keyword] like '%'+ keyword +'%' )
 
Share this answer
 
Comments
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.
SQL
WITH keywords AS
(
  SELECT
    keyword
  FROM
    keywordinfo
)

SELECT
  emailinfo.email
FROM
  emailinfo
WHERE
  emailinfo.email NOT LIKE '%' + keywords.keyword + '%'
 
Share this answer
 
v2
Comments
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 "emailinfo.email" 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
 
Comments
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