Boolean Full-Text searches and Field Scope

Boolean Full-Text searches and Field Scope

Post by B Lamme » Thu, 15 Aug 2002 00:27:19



I'm executing a containstable (I suppose I'd encounter the same situation if
I used Contains) like the following:

Select ID, 3 AS TC, Rank/10 as perRank from TableName,
CONTAINSTABLE(TableName, *,'red AND green') as K WHERE K.[KEY] = ID

And what I get are the records in TableName That have RED and GREEN in the
same field. Is there a trivial way to rephrase this query to get all the
records in TableName that have RED and GREEN in ANY Field, but somewhere
they all have BOTH RED and GREEN?

I've banged my head against this for a couple of days... everything I try
seems to return either records with RED or GREEN somewhere in them, or only
RED AND GREEN in the same field.

thanks in advance,

-bill

 
 
 

Boolean Full-Text searches and Field Scope

Post by Hilary Cotte » Thu, 15 Aug 2002 01:32:58


FreeText and FreeTextTable will give you matches that
occur accross fields.  Contains and ContainsTable won't.
Quote:>-----Original Message-----
>I'm executing a containstable (I suppose I'd encounter

the same situation if
Quote:>I used Contains) like the following:

>Select ID, 3 AS TC, Rank/10 as perRank from TableName,
>CONTAINSTABLE(TableName, *,'red AND green') as K WHERE K.
[KEY] = ID

>And what I get are the records in TableName That have RED
and GREEN in the
>same field. Is there a trivial way to rephrase this query
to get all the
>records in TableName that have RED and GREEN in ANY

Field, but somewhere
Quote:>they all have BOTH RED and GREEN?

>I've banged my head against this for a couple of days...
everything I try
>seems to return either records with RED or GREEN

somewhere in them, or only
Quote:>RED AND GREEN in the same field.

>thanks in advance,

>-bill

>.


 
 
 

Boolean Full-Text searches and Field Scope

Post by B Lamme » Thu, 15 Aug 2002 02:04:22


I considered that, but FreeText applied to the query effectively "ORs" the
search. Isn't returning the results i need.

thanks for you suggestion though!

-bill


Quote:> FreeText and FreeTextTable will give you matches that
> occur accross fields.  Contains and ContainsTable won't.
> >-----Original Message-----
> >I'm executing a containstable (I suppose I'd encounter
> the same situation if
> >I used Contains) like the following:

> >Select ID, 3 AS TC, Rank/10 as perRank from TableName,
> >CONTAINSTABLE(TableName, *,'red AND green') as K WHERE K.
> [KEY] = ID

> >And what I get are the records in TableName That have RED
> and GREEN in the
> >same field. Is there a trivial way to rephrase this query
> to get all the
> >records in TableName that have RED and GREEN in ANY
> Field, but somewhere
> >they all have BOTH RED and GREEN?

> >I've banged my head against this for a couple of days...
> everything I try
> >seems to return either records with RED or GREEN
> somewhere in them, or only
> >RED AND GREEN in the same field.

> >thanks in advance,

> >-bill

> >.

 
 
 

Boolean Full-Text searches and Field Scope

Post by Andy Wakelin » Fri, 16 Aug 2002 00:53:25


This has GOT to be a bug as I managed to get it working on
SQL 7.0 but when I moved over to SQL2000 I got the same
results as yourself. However, it did work VERY
occasionally when I tried it through QA but why it was
working when it did made no sense! at all! I haven't given
up on this as I need it so if (and when) I find an answer
I'll post it here.

Quote:>-----Original Message-----
>I considered that, but FreeText applied to the query

effectively "ORs" the
>search. Isn't returning the results i need.

>thanks for you suggestion though!

>-bill



>> FreeText and FreeTextTable will give you matches that
>> occur accross fields.  Contains and ContainsTable won't.
>> >-----Original Message-----
>> >I'm executing a containstable (I suppose I'd encounter
>> the same situation if
>> >I used Contains) like the following:

>> >Select ID, 3 AS TC, Rank/10 as perRank from TableName,
>> >CONTAINSTABLE(TableName, *,'red AND green') as K WHERE
K.
>> [KEY] = ID

>> >And what I get are the records in TableName That have
RED
>> and GREEN in the
>> >same field. Is there a trivial way to rephrase this
query
>> to get all the
>> >records in TableName that have RED and GREEN in ANY
>> Field, but somewhere
>> >they all have BOTH RED and GREEN?

>> >I've banged my head against this for a couple of
days...
>> everything I try
>> >seems to return either records with RED or GREEN
>> somewhere in them, or only
>> >RED AND GREEN in the same field.

>> >thanks in advance,

>> >-bill

>> >.

>.

 
 
 

Boolean Full-Text searches and Field Scope

Post by B Lamme » Fri, 16 Aug 2002 03:27:14


Thanks for taking the time to test/post the answer. Strange that you got
different results with QA - I will attempt to duplicate that!

regards,

-bill


> This has GOT to be a bug as I managed to get it working on
> SQL 7.0 but when I moved over to SQL2000 I got the same
> results as yourself. However, it did work VERY
> occasionally when I tried it through QA but why it was
> working when it did made no sense! at all! I haven't given
> up on this as I need it so if (and when) I find an answer
> I'll post it here.

> >-----Original Message-----
> >I considered that, but FreeText applied to the query
> effectively "ORs" the
> >search. Isn't returning the results i need.

> >thanks for you suggestion though!

> >-bill



> >> FreeText and FreeTextTable will give you matches that
> >> occur accross fields.  Contains and ContainsTable won't.
> >> >-----Original Message-----
> >> >I'm executing a containstable (I suppose I'd encounter
> >> the same situation if
> >> >I used Contains) like the following:

> >> >Select ID, 3 AS TC, Rank/10 as perRank from TableName,
> >> >CONTAINSTABLE(TableName, *,'red AND green') as K WHERE
> K.
> >> [KEY] = ID

> >> >And what I get are the records in TableName That have
> RED
> >> and GREEN in the
> >> >same field. Is there a trivial way to rephrase this
> query
> >> to get all the
> >> >records in TableName that have RED and GREEN in ANY
> >> Field, but somewhere
> >> >they all have BOTH RED and GREEN?

> >> >I've banged my head against this for a couple of
> days...
> >> everything I try
> >> >seems to return either records with RED or GREEN
> >> somewhere in them, or only
> >> >RED AND GREEN in the same field.

> >> >thanks in advance,

> >> >-bill

> >> >.

> >.

 
 
 

Boolean Full-Text searches and Field Scope

Post by John Kan » Fri, 16 Aug 2002 10:05:24


Bill and Andy,
I think you're both seeing variations of the same issue, depending upon SQL
Server versions (7.0 or 2000) and what Service Packs you may have applied to
either version. Please, review the following KB Article Q286787 FIX:
Incorrect Results From Full-Text Search on Several Columns at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q286787

Regards,
John


> Thanks for taking the time to test/post the answer. Strange that you got
> different results with QA - I will attempt to duplicate that!

> regards,

> -bill



> > This has GOT to be a bug as I managed to get it working on
> > SQL 7.0 but when I moved over to SQL2000 I got the same
> > results as yourself. However, it did work VERY
> > occasionally when I tried it through QA but why it was
> > working when it did made no sense! at all! I haven't given
> > up on this as I need it so if (and when) I find an answer
> > I'll post it here.

> > >-----Original Message-----
> > >I considered that, but FreeText applied to the query
> > effectively "ORs" the
> > >search. Isn't returning the results i need.

> > >thanks for you suggestion though!

> > >-bill



> > >> FreeText and FreeTextTable will give you matches that
> > >> occur accross fields.  Contains and ContainsTable won't.
> > >> >-----Original Message-----
> > >> >I'm executing a containstable (I suppose I'd encounter
> > >> the same situation if
> > >> >I used Contains) like the following:

> > >> >Select ID, 3 AS TC, Rank/10 as perRank from TableName,
> > >> >CONTAINSTABLE(TableName, *,'red AND green') as K WHERE
> > K.
> > >> [KEY] = ID

> > >> >And what I get are the records in TableName That have
> > RED
> > >> and GREEN in the
> > >> >same field. Is there a trivial way to rephrase this
> > query
> > >> to get all the
> > >> >records in TableName that have RED and GREEN in ANY
> > >> Field, but somewhere
> > >> >they all have BOTH RED and GREEN?

> > >> >I've banged my head against this for a couple of
> > days...
> > >> everything I try
> > >> >seems to return either records with RED or GREEN
> > >> somewhere in them, or only
> > >> >RED AND GREEN in the same field.

> > >> >thanks in advance,

> > >> >-bill

> > >> >.

> > >.

 
 
 

Boolean Full-Text searches and Field Scope

Post by B Lamme » Sat, 17 Aug 2002 00:02:39


John ,
I'm working on SQL 2K myself... that article is related to our issue but
with nuanced differences... (I'm looking for a boolean "AND" applied to 2
searchterms no matter which field the terms might appear). One of the
solutions they put forth (concatenating the fields that are indexed into 1
field) would work for us here but it would duplicate all the fields that I
still need preserved in their own fields. Hoping to avoid this if possible -
its my last resort solution.

Thanks for the link!

-bill


> Bill and Andy,
> I think you're both seeing variations of the same issue, depending upon
SQL
> Server versions (7.0 or 2000) and what Service Packs you may have applied
to
> either version. Please, review the following KB Article Q286787 FIX:
> Incorrect Results From Full-Text Search on Several Columns at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q286787

> Regards,
> John



> > Thanks for taking the time to test/post the answer. Strange that you got
> > different results with QA - I will attempt to duplicate that!

> > regards,

> > -bill



> > > This has GOT to be a bug as I managed to get it working on
> > > SQL 7.0 but when I moved over to SQL2000 I got the same
> > > results as yourself. However, it did work VERY
> > > occasionally when I tried it through QA but why it was
> > > working when it did made no sense! at all! I haven't given
> > > up on this as I need it so if (and when) I find an answer
> > > I'll post it here.

> > > >-----Original Message-----
> > > >I considered that, but FreeText applied to the query
> > > effectively "ORs" the
> > > >search. Isn't returning the results i need.

> > > >thanks for you suggestion though!

> > > >-bill



> > > >> FreeText and FreeTextTable will give you matches that
> > > >> occur accross fields.  Contains and ContainsTable won't.
> > > >> >-----Original Message-----
> > > >> >I'm executing a containstable (I suppose I'd encounter
> > > >> the same situation if
> > > >> >I used Contains) like the following:

> > > >> >Select ID, 3 AS TC, Rank/10 as perRank from TableName,
> > > >> >CONTAINSTABLE(TableName, *,'red AND green') as K WHERE
> > > K.
> > > >> [KEY] = ID

> > > >> >And what I get are the records in TableName That have
> > > RED
> > > >> and GREEN in the
> > > >> >same field. Is there a trivial way to rephrase this
> > > query
> > > >> to get all the
> > > >> >records in TableName that have RED and GREEN in ANY
> > > >> Field, but somewhere
> > > >> >they all have BOTH RED and GREEN?

> > > >> >I've banged my head against this for a couple of
> > > days...
> > > >> everything I try
> > > >> >seems to return either records with RED or GREEN
> > > >> somewhere in them, or only
> > > >> >RED AND GREEN in the same field.

> > > >> >thanks in advance,

> > > >> >-bill

> > > >> >.

> > > >.

 
 
 

Boolean Full-Text searches and Field Scope

Post by B Lamme » Sat, 17 Aug 2002 00:11:14


on further study - the following paragraph seems to sum it up... I agree
with everything up until the last 2 words... so it looks like if I roll back
to SQL7 I get what I want. :(

Using the asterisk (*) argument to search all columns results in a query
that looks for all occurrences in all columns of each single word of the
search condition. The full-text engine does not account for the columns in
the query. If the single words of the search condition are found in
different columns, it is counted as a match anyway, and that row is returned
in error.

Thanks again John!


> Bill and Andy,
> I think you're both seeing variations of the same issue, depending upon
SQL
> Server versions (7.0 or 2000) and what Service Packs you may have applied
to
> either version. Please, review the following KB Article Q286787 FIX:
> Incorrect Results From Full-Text Search on Several Columns at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q286787

> Regards,
> John



> > Thanks for taking the time to test/post the answer. Strange that you got
> > different results with QA - I will attempt to duplicate that!

> > regards,

> > -bill



> > > This has GOT to be a bug as I managed to get it working on
> > > SQL 7.0 but when I moved over to SQL2000 I got the same
> > > results as yourself. However, it did work VERY
> > > occasionally when I tried it through QA but why it was
> > > working when it did made no sense! at all! I haven't given
> > > up on this as I need it so if (and when) I find an answer
> > > I'll post it here.

> > > >-----Original Message-----
> > > >I considered that, but FreeText applied to the query
> > > effectively "ORs" the
> > > >search. Isn't returning the results i need.

> > > >thanks for you suggestion though!

> > > >-bill



> > > >> FreeText and FreeTextTable will give you matches that
> > > >> occur accross fields.  Contains and ContainsTable won't.
> > > >> >-----Original Message-----
> > > >> >I'm executing a containstable (I suppose I'd encounter
> > > >> the same situation if
> > > >> >I used Contains) like the following:

> > > >> >Select ID, 3 AS TC, Rank/10 as perRank from TableName,
> > > >> >CONTAINSTABLE(TableName, *,'red AND green') as K WHERE
> > > K.
> > > >> [KEY] = ID

> > > >> >And what I get are the records in TableName That have
> > > RED
> > > >> and GREEN in the
> > > >> >same field. Is there a trivial way to rephrase this
> > > query
> > > >> to get all the
> > > >> >records in TableName that have RED and GREEN in ANY
> > > >> Field, but somewhere
> > > >> >they all have BOTH RED and GREEN?

> > > >> >I've banged my head against this for a couple of
> > > days...
> > > >> everything I try
> > > >> >seems to return either records with RED or GREEN
> > > >> somewhere in them, or only
> > > >> >RED AND GREEN in the same field.

> > > >> >thanks in advance,

> > > >> >-bill

> > > >> >.

> > > >.

 
 
 

Boolean Full-Text searches and Field Scope

Post by Andy Wakelin » Sat, 17 Aug 2002 19:46:41


Nice one. Cheers for that link John.
Unfortunately I am using a dynamic query so I never know
whether it is going to be 'red and green', 'red or green'
or 'red and (green or blue)' etc. so I cannot use that
UNION business (or even "WHERE CONTAINS(*, 'red') AND
CONTAINS(*, 'green')" for that matter.

As for creating an extra "search text" field, I may have
to go down that route for now. However, all the full-text
indexed fields that I need to concatenate are of data type
ntext and I really want to do it all in SQL so that should
be fun!!
Cheers again.

 
 
 

Boolean Full-Text searches and Field Scope

Post by B Lamme » Sun, 18 Aug 2002 03:17:40


Possible Solution:

Its ugly, adds to the execute time, and i'm not convinced that the ranking
that gets returned is very accurate... but it returns the proper records as
far as I can tell - it was written by the guy who would have had to do the
reloading of all our data to create the concated "search field", so he had
plenty of motivation:

select IndexField, Rank/10 as perRank
from TableName,
CONTAINSTABLE(TableName, *, '"red" or "green" ') as K
where TableName.Refnum = (
 select IndexField
 from TableName
 where  contains(*,'"red"')
 and contains(*,'"green"')
) and
K.[key] = IndexField
order by perRank DESC

hth,

bill