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

How can I deploy 'private' gems that my apps can access but no one else can get?

ruby-on-rails ruby deployment heroku rubygems

I have several apps which I want to share some common code. I'd like to gem the code and share/version it across the apps that way, although I don't want to publish it to a public gem server.

Alfresco: changing date and time formats

date datetime alfresco

I have a few places where I need to change how the date format displays in my Alfresco share: