Combining free text tables

  • Hi,

    I have two tables with one to one relationship.

    Primary table

    ID (Primary key)

    Name  (varchar)

    .......

    Secondary table (this table actually consist additional info about the same record)

    ID (foreign key of Primary table)

    Profile (varchar)

    ........

    Not all the records can have additional information (meaning in the primary table can have 40 records and secondary can only have 10).

    If a user searches for a word then both primary as well as secondary table needs to be checked and all the results must be shown on the order of the key.

    <code>

    SELECT

      l.*,

      FROM FREETEXTTABLE(Details, *, @SearchText) AS S

      INNER JOIN Details l

         ON l.ListingID = s.

      LEFT JOIN FREETEXTTABLE(ExtraDetails, *, @SearchText) as SS

       ON l.listingid = ss.

    </code>

    This does not work.  Also how do I get values of key in free text tables?

    Any help is greatly appreciated.  Thanks in advance.

  • Check the syntax in Books Online,

    "freetext_string

    Is the text to search for in the specified column. Variables cannot be used. "

     

    --------------------
    Colt 45 - the original point and click interface

  • Actually, using a @variable does work (from BOL title "CONTAINS")

    H. Use CONTAINS with variables

    This example uses a variable instead of a specific search term.

    USE pubs
    GO
    DECLARE @SearchWord varchar(30)
    SET @SearchWord ='Moon'
    SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
    
    You're query not correct for what you're trying to do, 
    see the following blog entry for details: 
    
    
    
    SQL Server FTS across multiple tables or columns
    http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
    
    -- Multiple columns from multiple tables
    SELECT e.LastName, e.FirstName, e.Title, e.Notes, t.TerritoryID, A.[Rank]
       from Employees AS e, EmployeeTerritories t,
         containstable(Employees, Notes, 'BA') as A 
           where A. = e.EmployeeID and t.EmployeeID = e.EmployeeID
               order by A.[Rank] DESC
    
    
    Hope that helps!
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/
     
     


    John T. Kane

  • "Actually, using a @variable does work "

    In the context that the poster is trying to use it, no it doesn't.

    Using CONTAINS ( because neither FREETEXTTABLE or CONTAINSTABLE support the use of variables ) the original query might work if it's written like this ??

    SELECT
      l.* 
    FROM Details l
    INNER JOIN (
      SELECT ListingID FROM Details WHERE CONTAINS( *, @SearchText )
    ) as S
    ON l.ListingID = s.[ListingID]
    LEFT JOIN (
      SELECT ListingID FROM ExtraDetails WHERE CONTAINS( *, @SearchText )
    ) as SS
    ON l.listingid = ss.[ListingID]

     

    --------------------
    Colt 45 - the original point and click interface

  • In context of what the poster is trying to do, yes @variables DO work with CONTAINSTABLE or FREETEXTTABLE, for example:

    declare @SearchStr varchar(8000)

    SET @SearchStr = '"green" or "white"'

    SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK

      FROM authors as FT_TBL INNER JOIN

    (SELECT * FROM

         CONTAINSTABLE(authors,au_lname, @SearchStr)) AS KEY_TBL

     ON FT_TBL.au_id = KEY_TBL.

    /* -- returns:

    au_lname                                 au_fname             RANK       

    ---------------------------------------- -------------------- -----------

    Green                                    Marjorie             80

    White                                    Johnson              80

    (2 row(s) affected)

    */

    My point is that the @variables are supported in FTS queries and have been in both SQL 7.0 SP1 and in SQL 2000. This issue is not with the @variables, but with how their FTS query is written. I can't test the their query as I don't have a SQL Server instances accessable at this time, but they should re-write the query to match the above and re-test it.

    Regards,

    John

    SQL Full Text Search Blog

    http://spaces.msn.com/members/jtkane/


    John T. Kane

  • ... but you're using CONTAINSTABLE and the poster is using FREETEXTTABLE. Aren't these two different things, or are you saying that Books Online is wrong and FREETEXTABLE does support variables?

     

    --------------------
    Colt 45 - the original point and click interface

  • Hi all

    Thank you very much for your answers. 

    While I can use the variable and it works fine but the problem arises when the primary table does not contain the search word (only the secondary table contains the search words), I get no results.

    If I query only the secondary table I get results.  My question is how do I get the correct results?  If I use inner join then only records that are in both are retrieved.  What I need is all the records which meet the search criteria.  I would like to reiterate that the secondary table is an extension of primary table (secondary records  contains extra features of records in primary table)

    Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply