Problem solve Get help with specific problems with your technologies, process and projects.

ANDs or ORs in a multi-field search

I am developing a Web site using ASP and an Access database. I have a table that has fields like firstname, lastname, state, e-mail, address, etc. I want to give a search option which will have four inputs of firstname, lastname, state and e-mail, so that the user can specify one, two, three or four options, and the results would get displayed based on these criteria from the table. Now to implement it, I just want to use one select statement. I had used a statement in one of the projects like:

select * from table where 0=0 and --;

which used to do the same function, but I have forgotten how to give ANDs and ORs after 0=0. Can you guide me what to do? Thanks.

You're on the right track with the WHERE 0=0 part of the query. The 0=0 condition will always evaluate as true, and what you want is for this to be ANDed with any additional conditions that you generate into the query by your ASP script.

The next thing to decide is whether the remaining conditions should be ORed together. Suppose someone enters 'John' for firstname and 'Doe' for lastname in the search fields. Chances are, the intention here is to get results where both are true, and this means an AND. If you were to generate an OR between firstname and lastname conditions in this situation, you'd get John Smith and Jane Doe. To most users, entering something into multiple search fields implies an AND condition for more specific results.

I'm not an ASP programmer, so I'll try to write this with pseudocode --

select firstname, lastname, etcetera
  from yourtable
 where 0=0
<%IF form.firstname <> ''>
   and firstname like ''' + form.firstname + '%'''

<%IF form.lastname <> ''>
   and lastname like ''' + form.lastname + '%'''
<%IF form.state <> ''>
   and state like ''' + form.state + '%'''

<%IF form.email <> ''>
   and email like ''' + form.email + '%'''

Each IF statement checks to see whether the form field contains a non-zero-length string, and generates another AND condition if it does.

What you'll have to figure out here is how to generate the single quotes that surround the form field values in the AND condition. What you're trying to generate will look like

   and firstname like 'John%'

Note that the wildcard percent character is automatically tacked on behind the field values so that you can use the LIKE operator. This allows users of the search form to enter partial values.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.