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 + '%''' <%ENDIF> <%IF form.lastname <> ''> and lastname like ''' + form.lastname + '%''' <%ENDIF> <%IF form.state <> ''> and state like ''' + form.state + '%''' <%ENDIF> <%IF form.email <> ''> and email like ''' + form.email + '%''' <%ENDIF>
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.