Freetexttable

Okay I have finally found full text-searching and got it working with SQL2000.

Now it appears to be working correctly however I am having difficulty getting to grips with FREETEXTTABLE, and help/advice would be appreciated.

Now this is what I would like to do. One form field on a web page, passes over a search phrase which then want to use to search across all rows of my selected table and then return WEIGHTED results or RANKED results.

Now I have used CONTAINS etc and had fairly good results however assuming what I am reading via MSDN using FREETEXTTABLE will give me much better and fairly accurate results based on a whole search phrase entered by a user.

Incidentally should I use CONTAINSTABLE?

Quick Note: I am only wanting to search through one table, no more than 2000 rows.

So I have my table (lets call it Account_List) containing the following rows:

AccountID (int,Not Null) <-PrimaryKey
AccountName (char(50), Null)
AccountAddress1 (char(50), Null)
AccountAddress2 (char(50), Null)
AccountTown (char(50), Null)
AccountCounty (char(50), Null)
AccountPostcode (char(10), Null)
AccountTelephone (nvchar(50), Null)

I have a populated full-text catalog for the above table containing all of the rows.

Now what I want to do is say for example the user inputs the phrase 'Argos in Milton Keynes' I would like it to go an search each row and return weighted results based on that. In this case the key columns are AccountName and AccountTown.

Now the only example so far I have is:

USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO

Microsoft standard example, however I don't get why I would have to do an INNER JOIN, what am I missing here? Like said before I am only using one table.

I would like a example of a simple string to search my database from the user input form.

Can anyone enlighten me :)As far as JOINing goes this is from Microsoft:

Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.

For more examples :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_1m9f.asp|||Got to the bottom of it!

The correct Query is as follows for anyone wanting to know :eek:

USE mydata
GO
SELECT *
FROM Account_List AS FT_TBL
INNER JOIN
FREETEXTTABLE(Account_List, *,
'Argos in Milton Keynes') AS KEY_TBL
ON FT_TBL.AccountID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

Oh how I like technology (when it works :mad: )

Thanks!