SQL Server Performance

CONTAINS in INNER JOIN where a column needs to be passed as the string to be searched

Discussion in 'Getting Started' started by SuryaKiran, May 19, 2008.

  1. SuryaKiran New Member

    Hi All,
    I have a table tbl1 with these columns BodyText (freetext index added to enable search on this column(text datatype) of the table) and ArticleID INT PRIMARY KEY
    tbl2 stringtosearch varchar(250) , stringID INT PRIMARY KEY
    My query is
    SELECT t1,ArticleID
    , t2. stringID
    , t1.BodyText
    , t2.stringtosearch
    FROM tbl2 t2
    INNER JOIN tbl1 t1 ON CONTAINS(BodyText,t2.stringtosearch)
    The above query is returning me this error --
    Incorrect syntax near t2 on this line -- "INNER JOIN tbl1 t1 ON CONTAINS(BodyText,t2.stringtosearch)"
    Any help on this would be much appreciated.
    Thank in advance,
    Surya Kiran
  2. SuryaKiran New Member

    Hi All,
    I have corrected some basic syntactical mistakes but i am still left with the same syntax error.
    Here is my corrected code --
    I have a table tbl1 with these columns BodyText (freetext index added to enable search on this column(text datatype) of the table) and ArticleID INT PRIMARY KEY
    tbl2 stringtosearch varchar(250) , stringID INT PRIMARY KEY
    My query is
    SELECT t1.ArticleID
    , t2. stringID
    , t1.BodyText
    , t2.stringtosearch
    FROM tbl2 t2
    INNER JOIN tbl1 t1 ON CONTAINS(BodyText,t2.stringtosearch)
    The above query is returning me this error --
    Incorrect syntax near t2 on this line -- "INNER JOIN tbl1 t1 ON CONTAINS(t1.BodyText,t2.stringtosearch)"
    Any help on this would be much appreciated.
    Thanks in advance,
    Surya Kiran
    Hi All,
    I have a table tbl1 with these columns BodyText (freetext index added to enable search on this column(text datatype) of the table) and ArticleID INT PRIMARY KEY
    tbl2 stringtosearch varchar(250) , stringID INT PRIMARY KEY
    My query is
    SELECT t1.ArticleID
    , t2. stringID
    , t1.BodyText
    , t2.stringtosearch
    FROM tbl2 t2
    INNER JOIN tbl1 t1 ON CONTAINS(BodyText,t2.stringtosearch)
    The above query is returning me this error --
    Incorrect syntax near t2 on this line -- "INNER JOIN tbl1 t1 ON CONTAINS(t1.BodyText,t2.stringtosearch)"
    Any help on this would be much appreciated.
    Thank in advance,
    Surya Kiran
  3. Adriaan New Member

    My first guess would be that you cannot use CONTAINS in a JOIN clause (although BOL does not say so) so your first option would be to drop the JOIN and use something like:

    SELECT ..............
    FROM tbl2 t2, tbl1 t1
    WHERE CONTAINS(........................)
    BOL also states: "CONTAINS is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel."
  4. SuryaKiran New Member

    Hi Adriaan,
    Thank You for the quick reply.
    I tried the way that you have suggestedSELECT
    ....
    FROM tbl2 t2 , tbl1 t1
    WHERE CONTAINS(t1.BodyText,t2.stringtosearch) resulted in the same error --Incorrect syntax near 't2'. on Line 3 above.
    I am trying to use cursor and send in a variable but i am apprehensive that i will be having a performance setback , but would stick to using Cursors if they are the only way out of this problem.
    my code is based on the snippet in BOL of search phrase "CONTAINS"--
    H. Use CONTAINS with variables

    This example uses a variable instead of a specific search term.
    USE pubsGODECLARE @SearchWord varchar(30)SET @SearchWord ='Moon'SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
    I am using a cursor to achive the same .
    I got my results , and I am on my way to analyze how much correct the result set that I have achieved is ..
    Thank You very much,
    K.Surya Kiran.
  5. Adriaan New Member

    The examples in BOL have either a string expression between single quotes, or a variable, as the second parameter. Looks like you cannot use a column name as the second parameter.
    Have you tried using a LIKE clause with concatenation:

    WHERE col1 LIKE '%' + col2 + '%'

Share This Page