Rating based upon which column matches a string

Question

I am trying to write a query that rates how well a particular object matches some search criteria.

I have a table with the columns: objectid, typeid and name. An object can have two names associated with it (current name or previous name). The typeid identifies what type of name the row is.

A user can search the database from a form. The form submits two strings. I want to search the database and rows that match string1 and are a "current name" type be given a rating of 1. Rows that match string2 and are a "previous name" type also be given a rating of 1. Rows that match string1 but are a "previous name" type are still selected but are given a rating of 0. Rows that match string2 but are a "current name" type are still selected also but are given a rating of 0.

Currently I am searching for string1 and string2 where the type is "previous name" or "current name" and grouping the results by objectid and the rating is based on the count of each group. But this isn't flexible enough to give different ratings depending upon which column the string was found.

I have other criteria that will affect the rating. I am just having trouble trying to initially populate the temp table.

Answer 1

If you are looking for some aggregate counts, let me know, but this is how I interpreted your question:

select *,
    case
     when MyColumn = 'string1' and MyType = 'current name' then 1
     when MyColumn = 'string2' and MyType = 'previous name' then 1
     when MyColumn = 'string1' and MyType = 'previous name' then 0
     when MyColumn = 'string2' and MyType = 'current name' then 0
     else 2 -- in case type is neither previous nor current
    end as Rating
from MyTable
where MyColumn = 'string1'
    or MyColumn = 'string2'

Or like this if you only want records that match on one of the two types:

select *,
    case
     when MyColumn = 'string1' and MyType = 'current name' then 1
     when MyColumn = 'string2' and MyType = 'previous name' then 1
     when MyColumn = 'string1' and MyType = 'previous name' then 0
     when MyColumn = 'string2' and MyType = 'current name' then 0
    end as Rating
from MyTable
where (MyColumn = 'string1' or MyColumn = 'string2')
    and (MyType = 'current name' or MyType = 'previous name')