#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11

    Is Full Text CONTAINS on table join possible?


    Hey all,

    I have 2 tables: one is a lookup containing a series of keywords and the other is a list of articles. I want to join both tables together where the keywords (including INFLECTIONAL searches) using CONTAINS. I have tried the simple like operator but INFLECTIONAL works a treat as does NEAR so I am hoping to use both of those.

    Now, when I try to use a column name in the CONTAINS it isn't happy so I have to use text:e.g:

    DECLARE @v varchar(10);
    SET @v = 'dog';
    SELECT....CONTAINS(columnName, @v)

    where i had hoped to use the columnname from the lookup table where @v is...it doesn't appear from searching online that this is possible...so...I have considered using a simple cursor to loop through the lookuptable and then use the value as a variable for the CONTAINS but effectively that means iterating through hundreds of rows and then performing an update to tag the main table with the lookup value category...

    So is there a way of using the functionality in CONTAINS/CONTAINSTABLE to join 2 tables together on and update ? or is the cursor option the only real avenue left ?

    Cheers,
    B
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,174
    Rep Power
    4274
    Originally Posted by amstel_za
    So is there a way of using the functionality in CONTAINS/CONTAINSTABLE to join 2 tables together
    i think so, but what you posted is pretty vague

    would you care to share your actual table and column names?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Hey,

    Unfortunately I cannot provide the actual data but you are right - I was pretty vague Here's a simple sample of what I'd like to do.

    LookupTable
    __________

    LID, iValue, iLookupValue
    ------------------------

    1 FOOTBALL spotkick
    2 FOOTBALL shootout
    3 RUGBY dropgoal

    MainTable
    ________

    SID, SportArticle
    ------------------

    1 Farrell kicks winning dropgoal in thrilling final
    2 Rooney double with spotkick downs Liverpool
    3 Thrilling penalty shootout in Final


    The idea is to search within the SportArticle column for keywords in the lookup table and then associate each article with a sport...

    I've used LIKE but the problem with that is that it can return any number of matches for simpler words such as (non sport example here: war can match wars, warning, etc)...CONTAINS provides a lot more in INFLECTIONAL, THESAURUS and NEAR that give me options on matching criteria and also...more importantly ranking of matches...

    select * from lookup, maintable where CONTAINS(sportArticle, iLookupValue) > 0 just fails completely since I have read you can't use column names in the CONTAINS...so in order to use CONTAINS I've have to think about a cursor loop through the lookuptable on the maintable and that just seems super-inefficient...but...if it's the only way...

    REGEX has crossed my mind but I'd rather focus on the core functionality that SS provides first than try to build my own matcher.

    Does that make a bit more sense ?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,174
    Rep Power
    4274
    Originally Posted by amstel_za
    select * from lookup, maintable where CONTAINS(sportArticle, iLookupValue) > 0 just fails completely
    you tested this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Ah yes, good point! Syntax error:

    meant to say:

    Code:
    select * from lookup, maintable where CONTAINS(sportArticle, iLookupValue)
    returns incorrect syntax iLookupValue - if I change iLookupValue to text (e.g.: 'dropgoal') it return all the rows with that in it.
    Last edited by amstel_za; February 13th, 2012 at 05:33 AM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,174
    Rep Power
    4274
    bummer

    looks like you'll have to use a cursor and test each lookup value separately

    or revert to using LIKE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Originally Posted by r937
    bummer

    looks like you'll have to use a cursor and test each lookup value separately

    or revert to using LIKE
    Yep, I have tried LIKE but it doesn't give me the control I need for matching - looks like CURSOR it is

    Many thanx for taking the time out.

    Ben

IMN logo majestic logo threadwatch logo seochat tools logo