CODEWITHSUNDEEP
javamysqlsqlselectrequest
Mike_NotGuilty
Mike_NotGuilty

Reputation: 2405

SQL request - SELECT with multiple empty values

I have a database with multiple columns. Now i have a Java program where i can search for different rows in my database. But i need help for the SQL SELECT request. I have some texfields and some checkboxes. Now i want if the textfields are empty, or "any" is selected in one of the checkboxes , the select request should select ALL from this column.

Here is my request right now:

resultSet = sqlStatement.executeQuery("SELECT * FROM Messgerate WHERE COLUMN1 LIKE '%"+sColumn1+"%'" +
"AND COLUMN2 LIKE '%"+sColumn2+"%'" +
"AND COLUMN3 LIKE '%"+sColumn3+"%'" +
"AND COLUMN4 LIKE '%"+sColumn4+"%'" +
"AND COLUMN5 LIKE '%"+sColumn5+"%'" +
"AND COLUMN6 LIKE '%"+sColumn6+"%'" +"");

This request ist working if i type in something in all of the textfields and checkboxes.

Can someone help me please?

Upvotes: 1

Views: 199

Answers (3)

Hitesh
Hitesh

Reputation: 3498

select * from messgerate where case when isnull(sColumn1,'') = '' then '' when isnull(sColumn1,'') = 'any' then '' else COLUMN1 end LIKE '%"+sColumn1+"%'"

Repeat the case statement for the rest of the columns

Upvotes: 0

Ganesh Jadhav
Ganesh Jadhav

Reputation: 2848

You should pass an empty string when Any is selected in the dropdown. And then you can use this:

resultSet = sqlStatement.executeQuery("SELECT * FROM Messgerate WHERE COLUMN1 LIKE '%"+sColumn1+"%'" +
"OR COLUMN2 LIKE '%"+sColumn2+"%'" +
"OR COLUMN3 LIKE '%"+sColumn3+"%'" +
"OR COLUMN4 LIKE '%"+sColumn4+"%'" +
"OR COLUMN5 LIKE '%"+sColumn5+"%'" +
"OR COLUMN6 LIKE '%"+sColumn6+"%'" +"");

Upvotes: 0

Linga
Linga

Reputation: 10563

Can you replace AND with OR. It ll solve your issue. Because the AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

"SELECT * FROM Messgerate WHERE COLUMN1 LIKE '%"+sColumn1+"%'" +
"OR COLUMN2 LIKE '%"+sColumn2+"%'"
"OR COLUMN3 LIKE '%"+sColumn3+"%'" +
"OR COLUMN4 LIKE '%"+sColumn4+"%'" +
"OR COLUMN5 LIKE '%"+sColumn5+"%'" +
"OR COLUMN6 LIKE '%"+sColumn6+"%'" +"");

Upvotes: 1

Related Questions