December 17, 2002 at 12:07 pm
Hi,
I have the following SQL statement to return images that contain the word winter but NOT snow:
SELECT as RefNo, RANK, Description, Property, County, Format, Photographer, Artist, Classification, FiledUnder, Agency, Credit
FROM CONTAINSTABLE(Images, Description, 'WINTER AND NOT SNOW') S LEFT OUTER JOIN Images I ON S.[Key] = I.RefNo
ORDER BY Rank DESC
The above seems to work fine, lots of winter related results without any snow!. However, if I want to do the same search across all fields using:
SELECT as RefNo, RANK, Description, Property, County, Format, Photographer, Artist, Classification, FiledUnder, Agency, Credit
FROM CONTAINSTABLE(Images, *, 'WINTER AND NOT SNOW') S LEFT OUTER JOIN Images I ON S.[Key] = I.RefNo
ORDER BY Rank DESC
For some reason, with the above I end up with results containing the word SNOW. Why is this??????
Thanks for your help.
Tom
December 17, 2002 at 6:09 pm
Hello.
I believe CONTAINSTABLE is combining the results of individual searches on all of the columns that have been registered for searching. So, if Record 1 in 'OtherColumn' contains Winter and not Snow but does contain both Winter and Snow in 'Description', it will still be returned since the search in 'OtherColumn' was successful.
Everett Wilson
ewilson10@yahoo.com
December 18, 2002 at 3:23 am
So is there any way around that behaviour? Clearly it's not what's required so why does it work like this?
Thanks
Tom
December 18, 2002 at 3:28 am
How many of the columns within your table have full text indexes built on them?
December 18, 2002 at 3:31 am
All of them. 🙂
It's a conversion of an existing flat file system where all fields must be searchable.
Thanks
December 18, 2002 at 3:40 am
One solution would be to build a distinct list of Key values in a subquery using a union statement for each column you need to search. Then join this subquery onto your original table (and any others you require) and return the full result set.
SELECT KEY as RefNo, RANK, Description, .....
FROM (SELECT as RefNo
FROM Images
WHERE CONTAINS(column1, 'WINTER AND NOT SNOW')
UNION
SELECT as RefNo
FROM Images
WHERE CONTAINS(column2, 'WINTER AND NOT SNOW')
UNION SELECT as RefNo
FROM Images
WHERE CONTAINS(column3, 'WINTER AND NOT SNOW')
UNION ........) s LEFT OUTER JOIN Images i
ON s. = i.RefNo
ORDER BY Rank Desc
December 18, 2002 at 8:33 am
Will this not be incredibly slow? Given that there are approx 20,000 records? And surely the full text search system will run considerably faster using * than separate queries for each field?
That being said, if it doesn't work, it doesn't work so slow is better than not at all I guess.
December 18, 2002 at 8:57 am
Maybe an alternative solution could be to construct a composite column, containing a concatenation of all of your fields in the table.
If you only include that column for the full text index, you shouldn't get erroneous results.
You'll be eating up a lot of disk space though, if there are a lot of big columns.
December 18, 2002 at 9:02 am
Yikes - don't like the sound of that solution 🙂
Unfortunately it is a requirement, at times, to only search specific columns. The query is constructed dynamically :S
Thanks
Tom
December 18, 2002 at 9:55 am
I don't have a lot experience using the * with the Full-Text Search. But would performing two * searches be faster than what has been suggested? For example:
SELECT as RefNo, RANK, Description, Property, County, Format, Photographer, Artist, Classification, FiledUnder, Agency, Credit
FROM CONTAINSTABLE(Images, *, 'WINTER AND NOT SNOW') S
LEFT OUTER JOIN Images I
ON S.[Key] = I.RefNo
LEFT OUTER JOIN
(SELECT as RefNo
FROM CONTAINSTABLE(Images, *, 'SNOW') AS SnowValues
LEFT OUTER JOIN Images I ON SnowValues.[Key] = I.RefNo)
AS NotIncluded
ON I.RefNo = NotIncluded.RefNo
WHERE NotIncluded.RefNo Is Null
ORDER BY Rank DESC
Everett Wilson
ewilson10@yahoo.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply