sql server full text search: I have figured how that when i type in "bob and Joe" (and
i am using full text search ContainsTable with all
columns specified "*") that it will search each column
for the exact text of "bob and Joe".

My problem or question is how do I use fulltext if i want
to have sql return the results where bob would be in one
column and joe in another column?

Do i need to perform 2 Containstable queries while
specifying the specific columns in each query?

The problem is that i have a table with 20 columns and i
want the user to just type in one textbox and have sql
return any rows where bob shows in one column while joe
shows in another.

How best can i do this?
thank you

Dave,
Other than using multiple containstable predicates (that can also affect the
query performance), there are few alternatives, such as:

-- AND'ing CONTAINSTABLE for multiple FT enable columns in the same table!!
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'BA') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID

-------> the above query with RANK and Weight as well...

SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID


Note, each containstable is a "round-trip" to the FT Catalog and depending
upon the number of rows in your FT-enable table, this can affect the query's
performance. You should use "*" where you can to search all columns, and
then use, when necessary, a query with multiple containstable clauses.

Regards,
John



[quoted text, click to view]

I read the following somewhere on the web...

For Contains and ContainsTable you are correct. The
boolean AND will only work when both hits are in the same
column. For FreeText and FreeTextTable the query and
boolean operators can look across columns.

Is the something wrong with the above approach?
thx
dave

If it is ok, in addition to returning rows that have Joe AND Bob in
different columns, to also return rows with Joe AND Bob in the same column,
why not take the intersection of the key columns returned from:

CONTAINSTABLE(*, 'Joe')
CONTAINSTABLE(*, 'Bob')

As John points out this is in fact two round-trips to the catalog, but if
this is what you're really looking for then it could work -- just join the
two result sets together and then join the key back to the base table.

Alternately, if you can tolerate a bit fuzzier search, the FREETEXT style
queries could be a decent choice. It should be noted that while FREETEXT
appears to be able to "see across columns" that the statement is not
entirely correct. FREETEXT is more of an implicit OR plus stemming and a
different scoring algorithm. I'd encourage experimenting a bit with
FREETEXT in addition to considering the alternative above.

All the best,
--andrew

Andrew Cencini
Program Manger
Microsoft Corp. - SQL Server Engine

--
This posting is provided "AS IS" with no warranties, and confers no rights.


[quoted text, click to view]