February 10th, 2012, 10:30 AM
Is Full Text CONTAINS on table join possible?
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';
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 ?
February 10th, 2012, 08:40 PM
i think so, but what you posted is pretty vague
Originally Posted by amstel_za
would you care to share your actual table and column names?
February 13th, 2012, 03:03 AM
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.
LID, iValue, iLookupValue
1 FOOTBALL spotkick
2 FOOTBALL shootout
3 RUGBY dropgoal
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 ?
February 13th, 2012, 03:49 AM
you tested this?
Originally Posted by amstel_za
February 13th, 2012, 06:14 AM
Ah yes, good point! Syntax error:
meant to say:
returns incorrect syntax iLookupValue - if I change iLookupValue to text (e.g.: 'dropgoal') it return all the rows with that in it.
select * from lookup, maintable where CONTAINS(sportArticle, iLookupValue)
Last edited by amstel_za; February 13th, 2012 at 06:33 AM.
February 13th, 2012, 01:32 PM
looks like you'll have to use a cursor and test each lookup value separately
or revert to using LIKE
February 20th, 2012, 08:17 AM
Yep, I have tried LIKE but it doesn't give me the control I need for matching - looks like CURSOR it is
Originally Posted by r937
Many thanx for taking the time out.