Re: Free-Text Search "AND NOT" Wrong results - HELP, please!

joel wrote on Thu, 09 Aug 2007 08:06:01 -0700:

Thanks for your answer Dan.

I modified the query as follows:
SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
INNER JOIN CONTAINSTABLE(Fotos,*, 'Jane AND NOT clinic') AS
KEY_TBL1 ON Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank>0 AND KEY_TBL1.Rank>0
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC

I changed it to use Inner Join, but also ensured that any
Containstable returns significant photos, by having separate
Containstabel.Rank>0 in the WHERE clause. But it returns a row where a
field has something like "Simon and Jane entering the magnetic
resonance clinic...". I don't see any error on this new query...

I ended up solving this with and aditional NOT CONTAINS in the WHERE
clause:
SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
INNER JOIN CONTAINSTABLE(Fotos,*, 'Jane AND NOT clinic') AS
KEY_TBL1 ON Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank>0 AND KEY_TBL1.Rank>0 AND NOT
CONTAINS(Fotos.*,'clinic')
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC

There is no other way to , but I am concerned with performance.

Thanks for your help.

All the best,
JOEL


If you break your query up into just one clause for testing, does it still
pull up the wrong row?

SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]

If that still pulls up the row that has the word "clinic" in it, I can think
of only two reasons

(1) clinic isn't indexed - either it was added to the description after the
index was populated and the index has been repopulated, or maybe you have
the word clinic in your noise words file. However, your second query looking
for rows that don't have clinic in shows that this cannot be the case

(2) the AND NOT isn't working Can you try:

SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, '"Simon" AND NOT "clinic"')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]

If that works as expected, then go back to your original query and put all
your words inside double quotes. The documentation states that AND NOT is
valid, but maybe the FTS query processor is treating them as words.

Dan


.

Loading