Full-Text Search query format

Does the subquery

    SELECT [SearchWords] FROM [TWords] WHERE [GroupCode]=1

return multiple records? I'm guessing yes. Do you want to find all of the words (AND) or any of the words (OR) with exact matches? There are a *lot* of options.

Let's start simple and work up to your full problem. Tweak the following to return something, so we have a starting point that works.

  declare @words varchar(100)
  set @words = 'testing'

  SELECT [ID] FROM [TOutput] AS TO
  WHERE
    CONTAINS ( [TO].[OutputText], @words )

Then do a single-value lookup out of your SearchWords table instead of the constant value.

   set @words = (select top 1 [SearchWords] FROM [TWords] WHERE [GroupCode]=1)

Assuming that works, the real challenge will be to get a list of 1..N search words concatenated together with the AND / OR / NEAR operators.  A user-defined function can accomplish that pretty nicely.

-- Craig

Yes you are correct that SELECT [SearchWords] FROM [TWords] WHERE [GroupCode]=1 will return multiple records.

The example you gave where @words='testing' works however the select top 1 statement did not work.

use this
select top 1 @words =  [SearchWords] FROM [TWords] WHERE [GroupCode]=1
instead of
set @words = (select top 1 [SearchWords] FROM [TWords] WHERE [GroupCode]=1)
in Craig post.

TOP 1 needs an ORDER BY ... I was typing that off my head and did not test it. The point was to perform a SELECT and get a single search word from your table, so use whatever technique that works for you. This is an intermediate step. You must be able to use a single word from your search word list before moving on to handling multiple words.

I guess that works at least syntacticly but it seems one of my basic assumptions does not work.  That is the CONTAINS statement does not seem to return a result.  For example if the text field contained the word 'watch' and I did the query using 'watch' I get back no results.  So I guess I have a more fundamental problem.  Hence I'm doubling the points.

did you try using ' "*watch*" ' in search instead of 'watch'
======================================
declare @words varchar(100)

select top 1 @words =  [SearchWords] FROM [TWords] WHERE [GroupCode]=1
set @words = '"*'+ltrim(rtrim(@words))+'*"'

  SELECT [ID] FROM [TOutput] AS [TO]
  WHERE
    CONTAINS ( [TO].[OutputText], @words )

The query:

SELECT [ID] FROM [TOutput] AS TO
  WHERE
    CONTAINS ( [TO].[OutputText], 'watch' )

should work if the OutputText column does indeed contain 'watch'.

If it does not return rows (as opposed to displaying an error message) then the full-text catalog has been defined but the indexing job has never run or is scheduled to run but has not yet run, is scheduled to run but failed (check the job history) or ran successfully but is not set to update the index and hits for 'watch' were added or modified since the job was last run.

-- Craig

Sorry been away for a while.

Yes it works.  But the original problem of how to get all the words remains.

DECLARE @vsWord      varchar(128)

DECLARE cWords CURSOR FOR
SELECT sWord FROM TWords WHERE iID=1

SELECT sText INTO #tText FROM TData WHERE iID=0

OPEN cWords
FETCH NEXT FROM cWords INTO @vsWord
WHILE (@@FETCH_STATUS=0)
BEGIN
      INSERT INTO #tText SELECT sText FROM TData WHERE CONTAINS(tText,@vsWord)
      FETCH NEXT FROM cWords INTO @vsWord
END

CLOSE cWords
DEALLOCATE cWords

SELECT * FROM #tText GROUP BY sText
DROP TABLE #tText


The above accomplishes what I want but I wonder if there is a more efficient way of doing it?

>> yes it works

Good. I understand you want to do more. I'm trying to establish a baseline. No point in troubleshooting a complicated query if even a simple query won't work due to config problems.

>> Is there a more efficient way

Yes.

Next step is to verify that a query involving a list of words with OR works as desired. Below, a simulation of what happens when you use a GroupCode that returns multiple words.

   SELECT [ID] FROM [TOutput] AS TO
     WHERE  CONTAINS ( [TO].[OutputText], " 'watch' or 'other' or 'more' " )

If that works, convert the constant to a variable.

  declare @words varchar(1000)
  set @words = " 'watch' or 'other' or 'more' "
  SELECT [ID] FROM [TOutput] AS TO
     WHERE  CONTAINS ( [TO].[OutputText], @words )

Assuming it's still working, all you need to do is construct the list of words with OR between each. That can be done with a scalar user-defined function.  Below, the function is named dbo.WordListForGroup and is building it for GroupCode=1.

    SELECT [ID] FROM [TOutput] AS TO
     WHERE  CONTAINS ( [TO].[OutputText], dbo.WordListForGroup(1) )

Let me know if this is on the right track.

-- Craig

The problem I see is

declare @words varchar(1000)

In theory it is possible that I may need more than a 1000 char.  How can that be taken into account?

I picked 1000 out of the air.  In SQL 2000 the largest is:

  declare @words varchar(8000)

And in SQL 2005 there is no practical limit, something like 2 Gb.

  declare @words varchar(max)

Are you using SQL Server 2005? If so, building the list of words is much, much easier. In SQL 2000 building the word string requires a cursor.

-- Craig