Unexpected search results with 2+ words and 'AND'

[Mats]

Hello everyone !

I am using SQL Server 2005 and the index for full text search consists of 5
fields.
While there is everything fine searching only one word or searching several
words with OR, i encounter unexpected results while searching with two or
more words with AND.

The requirement is:
All given words have to be found but not necessarily in the same field.

At the moment i use the following statement for two words (i.e. "maier" and
"schmitt"):
SELECT TOP(1000000) s.[Pa1Nr] FROM [Sendedaten] AS s
WHERE ( s.[PA1Nr] IN (SELECT [PA1Nr] FROM [Sendedaten]
WHERE CONTAINS((alleTitel,ergAngaben,Pressetext),
'("maier")'))
AND s.[PA1Nr] IN (SELECT [PA1Nr] FROM [Pharos].[dbo].[Sendedaten]
WHERE CONTAINS((alleTitel,ergAngaben,Pressetext),
'("schmitt")')) )

Changing to the following statement the result set is smaller:
SELECT TOP(1000000) s.[Pa1Nr] FROM [Sendedaten] AS s
WHERE ( s.[PA1Nr] IN (SELECT [PA1Nr] FROM [Sendedaten]
WHERE CONTAINS((alleTitel,ergAngaben,Pressetext), '("maier"
AND "schmitt")')) )

The second result set is smaller because the two words are in the index for
the same "PA1Nr" (that's the id) but in different fields the index is built
of.

That's why I use the first statement.

Is this an intended behaviour?

Thanks for any advice in advance,
Mats

[Dan]

"Mats" <Ma...@discussions.microsoft.com> wrote in message
news:81BBFD21-EB5D-45B5...@microsoft.com...

Yes, this is intended behaviour. AND requires all terms to be in the same
column.

--
Dan