CONTAINSTABLE - weird results - using "and not"

Hello everyone,
I use full text search using containstable for search on my intranet
site. Its been working wonderfully. However, I have recently been
working on an upgrade to my search page to allow users to exclude
words. When excluding words I use the "and not" operator. I have
noticed that with some words it works, and with others it does not.
None of my words are noise or ignored words.
The below query returns 6 results (not using the excludes):
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )') KEY_TBL ON
FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN ICDB.dbo.LU_SearchCategories
LU_SearchCategories ON FT_TBL.Category = LU_SearchCategories.TypeID
WHERE FT_TBL.PermID <= (Select Users.Role from InfoCenter.dbo.Users
Users where Users.UID = 5432) and Category in (2,3) ORDER BY
KEY_TBL.RANK DESC
The top results in the query above returns a record that also contain
the words calculations and also the word integer. When I exclude
either of these words...it doesn't exclude that results from the
results.
Example using "and not"
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from ICDB.dbo.Users Users where Users.UID = 5432) and Category in (2,3)
ORDER BY KEY_TBL.RANK DESC
Further...when I include the word "calculations" in the search query as
a required word...it doesn't pull the record...actually..it doesn't
pull any records.
Example query:
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" ) AND (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC
The words "integer" and "calculations" are not the only words it does
this on...there are others.
Of course as I stated previously...some words to accurately exclude
those results...as in this case with the word "clmfmtdta". Query
example below.
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"clmfmtdta*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC
Does anybody have any ideas as to why this is doing this? Or maybe a
better way to use the "and not" operator?
I did a little more research...and I am thinking that because I use a
wildcard "*" to indicate the column, if say I used
CONTAINSTABLE(ICDB.dbo.SearchT=ADable, *, '(( "rte*" ) AND ( "billing*"
) AND ( "opt*" ) AND ( "editor*" )) and NOT ( "integer*" )')
Both rte, billing, opt, and editor would need to be in the same column
that integer is not in. So if rte, billing, opt, and editor were in
say the title column, and integer was in the description column...it
would not correctly filter out those records with integer in the
description.
Does this make sense? Any ideas?
|||Daniel,
Yes, it does. Unfortunately, the behavior is the "default" behavior for SQL
Server 2000 as SQL Server 7.0 was "fixed" to correspond to this same
behavior, i.e.., FT Search across column with or without the NOT
qualifier... Checkout the following two KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
For a possible workaround to this behavior, see the following blog entry:
"SQL Server FTS across multiple tables or columns" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
use Northwind
-- Multiple columns from one FT-enable table, modified to use the NOT
qualifier:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, '"University" and NOT "Lawrence"') as
A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<daniel.hirsch@.gmail.com> wrote in message
news:1123690134.517437.78570@.g43g2000cwa.googlegro ups.com...
I did a little more research...and I am thinking that because I use a
wildcard "*" to indicate the column, if say I used
CONTAINSTABLE(ICDB.dbo.SearchTXable, *, '(( "rte*" ) AND ( "billing*"
) AND ( "opt*" ) AND ( "editor*" )) and NOT ( "integer*" )')
Both rte, billing, opt, and editor would need to be in the same column
that integer is not in. So if rte, billing, opt, and editor were in
say the title column, and integer was in the description column...it
would not correctly filter out those records with integer in the
description.
Does this make sense? Any ideas?
|||Thanks..that does help...