Trying to write a query to look at business and address information

I have 1 input field for a user to type in either a business name, city and state, or zip code. I want to be able to pull back the correct data, but right now i'm getting incorrect results.

For example, if someone searches for "ERICS", it returns fine, but if someone searches for "ERICS ROCHESTER MN", 开发者_如何学Goit returns all of the results from ROCHESTER, MN. I want it to return no results in that case.

By the way, I am parsing out the data so i'm passing in up to 4 values in my query. business name, city, state, zip.

How can I modify my query so that it will give me the correct results? Can I somehow check in the query which variables are not null?

Schema

Business

business_id | name   | city        | state | zip
1             TOMS     ROCHESTER     MN      55906
2             BILLYs   MINNEAPOLIS   MN      55555
3             ERICS    LAX           WI      11111

Rating

rating_id | rating
1           GOOD
2           BAD

business_rating

br_id | business_id | rating_id
1       1             1
2       1             2




select b.business_id,  
       b.name, 
       b.city,
       b.state, 
       b.zip
       count(br.business_id) num_ratings, 
       round(avg(br.quality_id),2) quality_rating,   
       round(avg(br.friendly_id),2) friendly_rating,   
       round(avg(br.professional_id),2) professional_rating
from business b
   Left Join business_rating br
       On br.business_id = b.business_id
   Left Join rating r 
       On r.rating_id = br.quality_id
       And r.rating_id = br.friendly_id
 where (upper(b.business_name) like '%ERICS%'
        and upper(b.city) like '%ROCHESTER%'
        and upper(b.state) like '%MN%'
        and upper(b.zip) like '') 
 or (upper(b.city) like '%ROCHESTER%'
     and upper(b.state) like '%MN%'
     and upper(b.zip) like '')
 or (upper(b.city) like '%ROCHESTER%'
     and upper(b.state) like '%MN%')
 or (upper(b.zip) like '')
 group by id

Notice your WHERE clause - you're asking for a four-part match OR a three-part match, etc. That's why a three-part match (city, state, zip) is being returned.

The best way to handle this is to ship it out to a real search engine, e.g. solr.

Since you didn't ask for the best way, here's my answer to your question:

Write 4 queries.

One query for one-part requests, one query for two-part requests, etc. Have your code branch (or overload) based on how many parameters were provided.

This is assuming that, if there were no "ERICS", you would want to return no results, rather than showing some other bar in Rochester.

Oh, and don't apply functions (e.g. UPPER) to your columns - the engine will not be able to use your indexes to satisfy the query. Of course, using unanchored LIKE patterns will also preclude the use of indexes. Really, seriously, try solr.

Or, try this...

This query creates a *max_score* based on the number of fields that you've provided as non-null.

It calculates the number of columns that match the input, which is the score.

Then it only shows those rows whose score is the max.

set @NAME = "erics";
set @CITY = "rochester";
set @STATE = "MN";

select id,
case when @NAME is not null then 1 else 0 end +
  case when @CITY is not null then 1 else 0 end +
  case when @STATE is not null then 1 else 0 end +
  case when @ZIP is not null then 1 else 0 end as max_score,
case when @name is not null and name like concat("%", @NAME, "%") then 1 else 0 end +
  case when @city is not null and city like concat("%", @CITY, "%") then 1 else 0 end +
  case when @STATE is not null and state like concat("%", @STATE, "%") then 1 else 0 end +
  case when @ZIP is not null and zip like concat("%", zip, "%") then 1 else 0 end as score
from business
having score = max_score;

Now, please go install solr.