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

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

Answers


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

Need Your Help

Why won't my master table column increment in my trigger?

sql sql-server triggers

I need to create a trigger that will keep track of the number of times a movie is rented from a business like Blockbuster. I need a separate trigger for an insert, a delete and an update.

Value being inserted as 2147483647 into database

php mysql integer

All phone numbers I am trying to enter into my database are being inserted as 2147483647.