Is it possible to use CONTAINSTABLE to search for "word1" in column1 AND "word2" in column2

Question

We used to have a search, that checks two columns for some words. Both columns must contain some words provided, so we use AND ... no doubts FULLTEXT INDEX is used on the columns.

The select 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 CONTAINSTABLE functionality ... for only one column its simple. 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 it with 2 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 that was in where clause. Is there 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

Answer 1

You can do this with an inner join on a second containstable, although you might want to do something a bit fancier than adding the ranks together as I have in the sample

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