I have developed a search algorithm, which basically matches records on 3 different criteria types. Name, Address, and Keyword(s).

“doh” , will find:

• Name: doh
• Address: re
• Keywords: me

• Name: re
• Address: doh
• Keywords: me

• Name: re
• Address: me
• Keywords: doh

Now, when two or more records have a match, I am basically prioritising a single found entity, by means of checking how many times it was found.

My problem is, a single query will always only find one record, even it it matches on more than one criteria, so no prioritization can take place. I am currently building a single query, including all the keywords, for all 3 criteria fields. (Multiple AND / OR statements)

My current query looks like this:

Search Term: something somewhere shiny
Formulated Query:

select * from table where ((cl_address LIKE @something OR cl_name LIKE @something
OR cl_key_words LIKE @something) OR (cl_address LIKE @somewhere OR cl_name LIKE
@somewhere OR cl_key_words LIKE @somewhere) OR (cl_address LIKE @shiny OR cl_name LIKE 
@shiny OR cl_key_words LIKE @shiny))

To solve this, I would have to run a query for every criteria, for every keyword, for every address field, for every name…

This can result in multiple of 10 queries per search… although it “would” work…

How do the big boys do this?

P.S. The above is my own brain child… and not based on anything other than my own logic.. which may, or may not, be flawed 🙂