Hi

I'm using MSDE 2000 and have a table which contains a list of non stop words
inserted from another table. Now I need to query it but I'm unsure how best
to approach it. I need to return the unique IDs for a search string which
will contain multiple words. I know how to loop through the string to get
each word. The AND search seems to be the most difficult because I think I
need to 'query a query a query' for each word in the string. The table in
real life will contain many 10's of thousands of records so optimizing
performance is essential. Any guidance is very much appreciated.

Thanks
Andrew


CREATE TABLE Words (Word varchar(50), RecordID int)

INSERT INTO Words VALUES ('test',1)
INSERT INTO Words VALUES ('test',2)
INSERT INTO Words VALUES ('important',1)
INSERT INTO Words VALUES ('important',3)
INSERT INTO Words VALUES ('manufacture',2)

DECLARE @search_str varchar(100)
SET @search_str = 'important test'

/*
Loop through each word in @search_str

Results (AND search):

Word                                     RecordID
------------------------------ -----------
important                                1
test                                         1


Results (OR search):

Word                                     RecordID
------------------------------ -----------
test                                         1
important                                1
test                                         2
important                                3

Actually I just want the results to contain only the unique IDs. Do I use
the DISTINCT keyword or is there a better way?
*/

DROP TABLE #Words

syntax error in CONTAINS query
Calling of Store Procs on SQL Server from MS Access
Understanding best way to optimize...
need help with execution plan
Squeezing out more performance!!