Keyword searching across 2 tables

Hi,

I have a problem that is possibly been solved but I really need someone to direct me in the techniques for performing this task. I am using SQL Server but thought this is a generic SQL question and the application is a website.

I have a table of stock with summary text of type text. I have users who want to be alerted when their keywords are contained in a stock summary when a new stock is added to the database. (A user can have many keywords and they must all be contained in the summary)

Here comes my question, do I need to loop through ALL users and loop through each keyword a user has defined? Is there a standard technique for this kind of match?

I can only see 2 ways of doing this:

1. Store a unique array of keywords and the users that have them and do a match in code.
2. Loop on all users with like clause against the text of the stock, but not sure how this would work really.

I would appreciate feedback if anyone has done this kind of match (I may be missing something important and it may be easier than I think ?)

Many thanks in advance

Richard Received on Tue Jul 11 2006 - 05:38:00 CDT