Freetexttable

I am very new to the freetext searching and have not been
able to decipher some of the documentation steps in making
FreeTextTable work.
I have a table with a primary key of "KeyID", and my
search index is created on varchar fields of Problem and
Cause. I also have a field called WorkOrderID which is not
part of the index.
Basically I want to return the rank, the WorkOrderID, and
Problem/Cause if it matches. Pretty simple I should think.
I haven't found any line by line explanation of how the
query works.
It would be helpful if you could post the entire schema of this table. Here
is my stab in the dark as to what it would look like.
select KeyID, Problem, Cause, Rank from TableName join
FreeTextTable(TableName,*,'SearchPhrase') as a
on a.[key]=KeyID
order by Rank Desc
This will search for hits in any of the full text indexed columns, and will
search across columns. So if you are searching for James Bond, and one row
has the word James in the problem column and Bond in the Cause column this
will be a "hit"
If this won't work for you, you may have to do the more expensive:
select distinct KeyID, Problem, Cause, Rank=a.Rank +b.rank from authors,
FreeTextTable(TableName,Problem,'SearchPhrase') as a,
FreeTextTable(TableName,Cause,'SearchPhrase') as b where
a.[key]=KEYID or b.[key]=KEYID
order by Rank Desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:08f601c49cb2$87024080$a501280a@.phx.gbl...
> I am very new to the freetext searching and have not been
> able to decipher some of the documentation steps in making
> FreeTextTable work.
> I have a table with a primary key of "KeyID", and my
> search index is created on varchar fields of Problem and
> Cause. I also have a field called WorkOrderID which is not
> part of the index.
> Basically I want to return the rank, the WorkOrderID, and
> Problem/Cause if it matches. Pretty simple I should think.
> I haven't found any line by line explanation of how the
> query works.
>
|||I will try that also... table is:
KeyID - PK, int, identity
Problem - Varchar(2048)
Cause - varchar(2048)
WorkOrderID - int
All fields allow nulls except the PK of course.