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?

.

Relevant Pages

  • Re: How to combine existing collections to include or exclude...
    ... Here is a working query: ... machines to exclude, the primary collection would be modified as well. ... Servers you want minus the ones you don't want ... Add computers you don't want to show up in Collection B ...
    (microsoft.public.sms.admin)
  • RE: filter for values in another table
    ... But you indicated that you wanted to exclude all of the values where the ID ... SELECT TableA.* ... Would I just enter all of the ids into the parenthesis? ... runs is to create a query that some people call a "frustrated outer join". ...
    (microsoft.public.access.queries)
  • Re: Help with counting only one time
    ... You want to exclude all but the first row for each order? ... In the following example, I will assume there is a primary key field named ID, and the table is called Table1. ... but I am clueless on what to do in the query even ...
    (microsoft.public.access.queries)
  • Re: Criteria & <>
    ... "Ken Snell MVP" wrote: ... created a query where I had to put the same basic criteria in both fields. ... exclude certain records. ...
    (microsoft.public.access.queries)
  • Re: show the attributes of the selected records
    ... I tried your codes. ... What I want is to exclude the date if there is any bad quality data in that ... but I don't know how to realize it using query. ... Did putting the subquery in the Join run significantly ...
    (microsoft.public.access.queries)