AND statement for multiple columns in fulltext index

Question

I have a fulltext indexed table and try to query for results matching multiple words. E.g. I have a address table with the indexed columns address_text, zip_code and city.

| ROW | address_text   | zip_code | city       |  
| 1   | Bourbon street | 1234     | Baltimore  |  
| 2   | Bourbon street | 1234     | New Orleans|

Now I want to search for "Bourbon Baltimore" and only wants the first row.

I tried the following:

SELECT FT_TBL.* FROM ADDRESSES AS FT_TBL 
INNER JOIN CONTAINSTABLE(ADDRESSES, *, '"Bourbon*" AND "Baltimore*"') AS KEY_TBL 
ON FT_TBL.address_id = KEY_TBL.[KEY] 
ORDER BY KEY_TBL.RANK, address_text

But it will not return any rows at all.

Answer 1

I found this an interesting question so I decided to go and read up on "CONTAINSTABLE". So, I see that it is a Transact SQL function. If I'm reading the documentation correctly, it looks like the *contains_search_condition* (i.e., '"Bourbon*" AND "Baltimore*"') is applied to each column separately. So, you would probably need to join two CONTAINSTABLE functions together to gain the desired effect.

Each CONTAINSTABLE would need to specify one of the search conditions unless you wanted to search for each of them in each of the columns. The AND would need to become an OR in this case.