Is it possible to use CONTAINSTABLE to search for "word1" in column 1 and "word2" in column2
We had a search that checks two columns for some words. Both columns must contain some of the supplied words, so we use AND ... no doubt about it. FULLTEXT INDEX is used for columns.
The choice is more or less like this:
SELECT
*
FROM SomeTable
WHERE (CONTAINS(Column1, 'word1 OR word2') AND CONTAINS(Column2, 'word3 OR word4'))
now we need to add ranking to the result. We would like to use the CONTAINSTABLE ... functionality for just one column. Something like:
SELECT
SomeTable.*,
tmp.RANK
FROM SomeTable
INNER JOIN CONTAINSTABLE(SomeTable, Column1, 'word1 OR word2') as tmp
ON tmp.[KEY] = SomeTable.ID
Is it possible to do this with two columns, but consider that I only need to search for word1 or word2 in column1 (not interested if we have word1 or word2 in column2). Also consider AND what was in that place. There is something like:
SELECT
SomeTable.*,
tmp.RANK
FROM SomeTable
INNER JOIN CONTAINSTABLE(SomeTable, (Column1, Column2), 'column1:(word1 OR word2) AND column2:(word3 OR word4)') as tmp
ON tmp.[KEY] = SomeTable.ID
source to share
You can do this with an inner join on the second constrained, although you can do something a little more interesting than adding rows together like I have in the example
SELECT
SomeTable.*,
col1.RANK + col2.RANK
FROM
SomeTable
INNER JOIN CONTAINSTABLE(SomeTable, Column1, 'word1 OR word2') as col1 ON
col1.[KEY] = SomeTable.ID
INNER JOIN CONTAINSTABLE(SomeTable, Column2, 'word3 OR word4') as col2 ON
col2.[KEY] = SomeTable.ID
source to share