Reputation: 43
I don't understand why I'm getting these results with my full-text search query in SQL Server 2014.
The following query returns results with "Supervisor" in the Title
field and "Tokyo" in the HTML_Description
field.
SELECT
*
FROM post
JOIN CONTAINSTABLE([post], (Title, HTML_Description), 'Supervisor AND NOT Tokyo')
AS tb1 ON tb1.[Key] = post.ID
If I remove the HTML_Description
field in the CONTAINSTABLE
like this:
SELECT
*
FROM post
JOIN CONTAINSTABLE(post, (Title), 'Supervisor AND NOT Tokyo')
AS tb1 ON tb1.[Key] = post.ID
The result is the same.
If I use only the HTML_Description
field in the CONTAINSTABLE
like this:
SELECT
*
FROM post
JOIN CONTAINSTABLE(post, (HTML_Description), 'Supervisor AND NOT Tokyo')
AS tb1 ON tb1.[Key] = post.ID
There aren't any results.
How can I apply the NOT
operator to all fields?
Upvotes: 2
Views: 227
Reputation: 21264
Why does this happen? Because even when searching across multiple columns, the full text search conditions must be satisfied within a single column. Thus your query is equivalent to:
SELECT
*
FROM post
JOIN CONTAINSTABLE([post], (Title), 'Supervisor AND NOT Tokyo')
AS tb1 ON tb1.[Key] = post.ID
UNION ALL
SELECT
*
FROM post
JOIN CONTAINSTABLE([post], (HTML_Description), 'Supervisor AND NOT Tokyo')
AS tb1 ON tb1.[Key] = post.ID
Ways to work around this:
You should combine Title
and HTML_Description
into a single column (you can even create a computed column to do this) and create the full text index on this column.
Worth mentioning: In most cases you can rewrite the query using 2 or more CONTAINSTABLE
or CONTAINS
statements to search each keyword individually -- CONTAINS(*, 'apples') and CONTAINS(*, 'oranges')
instead of CONTAINS(*, 'apples AND oranges')
-- but then you'll either get multiple meaningless Rank
columns (because each is calculated against a single keyword) or you won't get a Rank
at all (if you use CONTAINS
). The link below shows a simple example of this. However this doesn't apply to your case because of your use of NOT. You cannot perform a full text search using only a NOT condition -- CONTAINS(*, 'NOT Tokyo')
will throw an error.
See Full-Text Search Queries with CONTAINS Clause Search Across Columns
Upvotes: 2