SQL search to return if several user inputs are empty

Question

Here is my dillemma, I am trying to implement search for my website, and what I have is multiple comboboxes, and the user can select an option and press search, I do however want to at least return what exists based on their selection, i.e if they leave section blank, and only select semester, then I should have something in my result set, or if they select semester, and instructor, and leave the others blank, then I should have something in my result set, unfortunately its not working. Here is my Query.

SELECT * 
  FROM CoursesTaught c 
 WHERE c.Section = :section 
       AND c.CourseName=:courseName 
       AND c.Semester=:semeste 
       AND c.programName=:ProgramName 
       AND c.CoordinatorName=:coordinatorname

Essentially what I want to do is do a check that if empty, or "n/a" then rather than 'AND' do an 'OR' but I can't figure it out.

@Joe what I mean by that is at least the ones that matched should appear. If section = '1' and semester=""(i.e left blank), then it should not return 0, it should at least return all the sections that are 1. — user2752552, Sep 12 '13 at 13:11
@user2752552 You need to do proper validation of input from user and based on the input, prepare your query using concatenation. My guess is that you are directly using conditions for ll fields from form in query even if those fields are left empty by user — rakeshjain, Sep 12 '13 at 13:25

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

Build your query dynamically depending on user selection. Similar question with answer how to do this in Java: How to dynamically generate SQL query based on user’s selections?

edited May 23 '17 at 11:56

Community

1
1

answered Sep 12 '13 at 13:14

Grygoriy Gonchar

3,898
1
24
16

score 0 · Answer 2 · answered Sep 12 '13 at 13:19

0

Unless you want to have a really ugly SQL query, you need to use a server side language to evaluate the user selections and build your query dynamically as a query string based on the users selection.

answered Sep 12 '13 at 13:19

Jared_S

166
6

score 0 · Accepted Answer · answered Sep 12 '13 at 13:28

0

Probably easiest to use the union operator:

http://www.w3schools.com/sql/sql_union.asp

SELECT * FROM CoursesTaught c
WHERE c.Section = :section
union
SELECT * FROM CoursesTaught c
WHERE c.Semester=:semeste

answered Sep 12 '13 at 13:28

Arjun Sol

731
5
18

score 0 · Answer 4 · answered Sep 12 '13 at 13:41

You can create query string manually i.e.

String query = "SELECT * FROM CoursesTaught c ";
String whereCondition = "";

if(!section.isEmpty)
  if(whereCondition.isEmpty)
     whereCondition = whereCondition + " AND c.Section = '" + section + "'";
  else
     whereCondition = "c.Section = '" + section + "'";

if(!courseName .isEmpty)
  if(whereCondition.isEmpty)
     whereCondition = whereCondition + " AND c.CourseName  = '" + courseName  + "'";
  else
     whereCondition = "c.CourseName  = '" + courseName  + "'";
.
.
.

if(!whereCondition.isEmpty)
   query = query + whereCondition;

And you can pass query string to your executeQuery() method.

score 0 · Answer 5 · answered Sep 12 '13 at 13:41

You'll probably see the best performance using the method suggested by Jared_S but you can do it with regular sql:

SELECT * 
  FROM CoursesTaught c 
 WHERE (c.Section = :section or :section is null)
       AND (c.CourseName=:courseName or :courseName is null)
       AND (c.Semester=:semester  or :semester is null)
       AND (c.programName=:ProgramName or :programName is null)
       AND (c.CoordinatorName=:coordinatorname or :coordinatorName is null)

This assumes that the missing parameters are null, if not simply change the test to look for an empty string, assuming that's the parameter type.

One drawback to this approach is that you'll end up returning the entire table if all the parameters are null.

SQL search to return if several user inputs are empty

5 Answers5