[thelist] writing a productive search query

Brian Cummiskey Brian at hondaswap.com
Mon Feb 28 15:32:09 CST 2005


Hi everyone-

I've been working on a customer search most of the morning, and i can't 
seem to come up with anything better.

here's the asp/sql code:


sSQL =	"SELECT CallerID AS ID, caller_first_name AS FirstName, 
caller_last_name AS LastName, caller_city AS City, caller_state AS 
State, caller_phone_number AS PhoneNumber FROM tCallerData where"

sSQL = sSQL & " (caller_first_name LIKE '%"& 
request("caller_first_name") &"%' AND caller_last_name LIKE '%"& 
request("caller_last_name") &"%')"

sSQL = sSQL & " OR caller_phone_number LIKE '%"& 
request("caller_phone_number")&"%'"

sSQL = sSQL & " OR (caller_state = '"& request("caller_state") &"' AND 
caller_last_name LIKE '%"& request("caller_last_name") &"%')"

sSQL = sSQL & " ORDER BY caller_state, caller_last_name, 
caller_first_name ASC"


My target here is for our phone rep to fill out a form on the previous 
page with the caller fn/ln/state/phone and then, select from the databse 
the most likely customer based on the criteria.

(Basic table structure is in the select clause.)

I've decided to avoid a query on a city, as it may yeild too many 
results.  As I look at the query more and more, I see myself in the 
position where this query may end up throwing the agent 1000's of 
records of previous callers.

How can i adjust the query so that it is inclusive of all, but not 
overkill once the database starts to grow (and it will by the hundreds 
per day)?

Any suggestions or ideas would be appreciated.






More information about the thelist mailing list