Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Fulltext Search Help

    Can anyone point me to some really good MS SQL FullText Search tutorials please?
    I've created my Full Text Catalog and now need to impliment my Classic ASP Search Page to use it.
    Im using Dreamweaver 8.02 to create my Recordsets and Select Statements.
    I've already found some tutorials, but so far, all of them fall short of really being much help.

    Hope someone can point me in the right direction

    Thanks
    Andy

  2. #2
    Join Date
    Feb 2008
    Posts
    120
    So far i have this Select Statement below. The problem is that i have to filter the results by a ClientID in another table called ClientProducts.
    How do i write that in to my Select Statement?
    Also, would there be an issue replacing 'my search variable', with the actual variable Request("searchterm") ??

    SELECT TOP (100) PERCENT FT_TBL.ProductID, FT_TBL.Product, KEY_TBL.RANK
    FROM dbo.Products AS FT_TBL INNER JOIN
    FREETEXTTABLE(dbo.Products, Product, 'my search variable here') AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY]
    ORDER BY KEY_TBL.RANK DESC

    Thanks
    Andy

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    so you want your select something like this for limiting it on client id
    Code:
    SELECT TOP (100) PERCENT FT_TBL.ProductID, FT_TBL.Product, KEY_TBL.RANK
    FROM dbo.Products AS FT_TBL INNER JOIN
    FREETEXTTABLE(dbo.Products, Product, 'my search variable here') AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY]
    INNER JOIN CLIENTPRODUCTS ON FT_TBL.ProductID = CLIENTPRODUCTS.PRODUCTID
    ORDER BY KEY_TBL.RANK DESC
    WHERE CLIENTPRODUCTS.CLIENTID=<clientID>
    for you replacement of variable stuff... I assume you are building this select statement in code using standard string concatenation so you would have something like...
    Code:
    dim clientId, variable, sqlStatement
    clientId = Request("clientId") 
    variable = Request("searchterm") 
    sqlStatement "SELECT TOP (100) PERCENT FT_TBL.ProductID, FT_TBL.Product, KEY_TBL.RANK FROM dbo.Products AS FT_TBL INNER JOIN FREETEXTTABLE(dbo.Products, Product, '" + variable + "') AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY]
    INNER JOIN CLIENTPRODUCTS ON FT_TBL.ProductID = CLIENTPRODUCTS.PRODUCTID
    ORDER BY KEY_TBL.RANK DESC WHERE CLIENTPRODUCTS.CLIENTID=" + clientId
    response.write sqlStatement
    The response.write at the end will help you troubleshoot. once it is working you can comment it out.

Posting Permissions

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