[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