Free-Text Search "AND NOT" Wrong results - HELP, please!
-
Hi,
I have a Photo Database, with a Photo table. Each photo has several Varchar fields for storing caption, description, keywords, etc. I have a Full-Text index/catalog for these fields.
This is the problem:
I want to find all photos that are related to "Simon" and "Jane", but no photos taken when they entered or exited the "clinic".
So, my Query looks like:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'ISABOUT("Simon") AND ISABOUT("Jane") AND NOT ISABOUT("clinic")') AS KEY_TBL ON Fotos.Cod = KEY_TBL.
WHERE KEY_TBL.Rank>0
ORDER BY ISNULL(KEY_TBL.Rank,0) DESC, ISNULL(Fotos.FotoDate, Fotos.CreatedDate) DESC
The problem is that it returns photos with descriptions that include the word "Clinic" (in this particular query, the first row in the resultset has the word clinic in it!).
I noticed "Simon", "Jane" and "clinic" could reside on different columns, so I rewrote the query:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic') AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.
LEFT JOIN CONTAINSTABLE(Fotos,*, Jane AND NOT clinic') AS KEY_TBL1 ON Fotos.Cod = KEY_TBL1.
WHERE KEY_TBL0.Rank+KEY_TBL1.Rank>0
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate, Fotos.CreatedDate) DESC
The same problem occurred.
What Am I doing wrong?
Thanks for your help.
JOEL
-
Don't you need to give relative waiting to each of the keywords towards the fuzzy match with two words in the search pattern ?
The left joins are ugly in that they match all photos to your full text search then filter based on the where clause (ie you dont need to join on keys without simon or Jane.)
SELECT *
FROM Fotos JOIN
(SELECT KEY_TBL. as Key, KEY_TBL.Rank as Rank
FROM CONTAINSTABLE(Fotos,*, 'ISABOUT(Simon (0.5), Jane (0.5))') AS KEY_TBL
WHERE KEY_TBL.Rank > 0 ) as X ON X.key = fotos.cod
WHERE fotos.cod not in
(SELECT KEY_TBL.
FROM CONTAINSTABLE(Fotos,*, 'ISABOUT(Clinic)') AS KEY_TBL
WHERE KEY_TBL.Rank > 0 )
ORDER BY X.Rank DESC, ISNULL(Fotos.FotoDate, Fotos.CreatedDate) DESC
Your exclusion predicate is ugly is there any easier way to identify records with 'clinic' ie can you
WHERE fotos.column_name not like '%clinic%'
I'd dare say that a not like would be quicker than a fuzzy search of all records and a not in.
-
Thanks for your idea.
Your solution doesn't account for situations where "simon" and "Jane" appear on different columns of the row ("Simon" on one field and "Jane" on another), which is a requirement.
Furthermore, the "WHERE fotos.cod not in " doesn't seem like a good solution to me, performance wise.
I ended up writing in in the following way:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'Simon') AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.
LEFT JOIN CONTAINSTABLE(Fotos,*, Jane') AS KEY_TBL1 ON Fotos.Cod = KEY_TBL1.
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
This way all the fotos have to rank, even if "Simon" and "Jane" reside on differenmt columns, plus I put the NOt condition in the WHERE clause, using the full-text search CONTAINS function. Upon testing, it gave very fast results. I hope I am not missing anything...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply