AND statement for multiple columns in fulltext index

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     | Ne开发者_Python百科w 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.

---

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.

---

It seems to me the FREETEXT command may be your simplest option, perhaps used in conjunction with CONTAINSTABLE if needed.

SELECT *
FROM ADDRESSES
WHERE FREETEXT (*, 'Bourbon Baltimore' ) --no wildcards possible

---

I'm probably too late but I have the same problem. The lame and ugly solution that I came with is the following.

Define an indexed view over the table, with one column being the primary key, the second a concatenation of all the other columns of interest, then a fulltext index on the view. The searches are done on the view with the same syntax '"term1" and "term2"'

so in this case it would be

CREATE VIEW vFoo
WITH SCHEMABINDING
AS 
SELECT ROW, ISNULL(address_text,'') + ' ' 
+ ISNULL(cast(zip_code as varchar(xx)),'') + '' as SearchText
 FROM addresses

CREATE UNIQUE CLUSTERED INDEX uci_bar ON vFoo 
(
    Row ASC
)

and then the full text index on the view