Results 1 to 3 of 3

Thread: Freetexttable

  1. #1
    Join Date
    Dec 2001
    Posts
    2

    Question Unanswered: 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

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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/de...qd_15_1m9f.asp

  3. #3
    Join Date
    Dec 2001
    Posts
    2
    Got to the bottom of it!

    The correct Query is as follows for anyone wanting to know

    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 )

    Thanks!
    Last edited by Will; 12-19-01 at 10:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •