Relevance Sorting...

[droppedeye]

I am trying to write a very simple search page where a user can search for images stored in the database by typing a search term. Images in the database have three attributes that lend themself to searching: Category, Keyword, and Description. I want to compare the search term the user entered to each one of the fields.

Now all that is just fine, except I want the results to be sorted by relevance. In this situation, I have defined the relevance scale to be 3 points if found in Category, 2 points if found in Keyword, and 1 point if found in Description. This means the highest possible relevance for an image would be 6, if the search term was found as a Category, Keyword, and Description of that image. For example, an image with the keyword "deer" (2 points) would be returned before an image with the description "Field of deer grass" (1 point).

Here is what I have so far, its close but its actually returning a result for each time an image is found in different attributes. For example, the query is returning...

WIID: 2 Relevance: 3
WIID: 2 Relevance: 2
WIID: 3 Relevance: 2
WIID: 4 Relevance: 2
WIID: 2 Relevance: 1
WIID: 3 Relevance: 1
WIID: 5 Relevance: 1

...when it should return

WIID: 2 Relevance: 6
WIID: 3 Relevance: 3
WIID: 4 Relevance: 2
WIID: 5 Relevance: 1

I apologize in advance for the size of the query, hopefully someone can make sense of it and provide a solution for me! I am using MSSQL 2000 if that is helpful. Thanks!

SELECT Image.WIID, (CASE WHEN Title LIKE '%deer%' THEN 2 ELSE 0 END) AS relevance 
FROM Image INNER JOIN ImageHasKeywords ON Image.WIID = ImageHasKeywords.WIID INNER JOIN Keyword ON ImageHasKeywords.KeywordID = Keyword.KeywordID 
WHERE Title LIKE '%deer%' GROUP BY Image.WIID, Title

UNION 

SELECT Image.WIID,(CASE WHEN Description LIKE '%deer%' THEN 1 ELSE 0 END) AS relevance 
FROM Image INNER JOIN ImageDetails ON Image.WIID = ImageDetails.WIID 
WHERE Description LIKE '%deer%' 

UNION 

SELECT Image.WIID, (CASE WHEN Title LIKE '%deer%' THEN 3 ELSE 0 END) AS relevance 
FROM Category INNER JOIN ImageBelongsToCategory ON Category.CategoryID = ImageBelongsToCategory.CategoryID INNER JOIN Image ON ImageBelongsToCategory.WIID = Image.WIID 
WHERE Title LIKE '%deer%' GROUP BY Image.WIID, Title 
ORDER BY relevance DESC

 

[r937]

select WIID
     , sum(relevance) as total_relevance
  from (
       [i]your union query goes here[/i]
       ) as d
group
    by WIID

r937.com | rudy.ca

 

[droppedeye]

Thanks for the quick response. I was led to believe MSSQL 2000 didn't support sub queries like this. When I try it this way (I have already tried this) it just gives me a syntax error. Maybe it is from a stray mark, I'm not sure... any other suggestions would be helpful.

 

[droppedeye]

Sorry, spoke too soon. This works, thanks so much! I am very grateful for this forum. I don't know why it didn't work before, the only difference this time around was the "as d GROUP BY WIID".

Oh and I guess you can't believe everything you hear.

 

[r937]

give me a hint -- what was the error?

by the way, you should use UNION ALL instead of UNION -- your relevance values are distinct, so you don't really need to sort them looking for dupes (and if dupes did exist, which would be the case if the relevance values weren't distinct, you'd get the wrong answer)

r937.com | rudy.ca

 

[droppedeye]

It was just a syntax error ')' or something like that. I double checked to make sure I didnt have a stray paren somewhere. I guess I must have though.

Thanks for the UNION ALL advice as well.