multiple search criteria

Question

I'm using the following query, but I currently have to enter a value in every parameter for the query to work. Is there a way of making the parameters optional, so that 1 or more values will return a result?

SELECT * FROM film
WHERE day LIKE '%day%'
AND month LIKE '%month%'
AND year LIKE '%year%'

thanks

Answer 1

SELECT * FROM film
WHERE day LIKE '%day%'
OR month LIKE '%month%'
OR year LIKE '%year%'

You will still be providing all three parameters, but it won't matter if they are blank or NULL, matches will still return from the ones that aren't blank.

Answer 2

erm this is working with

SELECT * FROM film
WHERE day LIKE '%day%'
OR month LIKE '%month%'

but

SELECT * FROM film
WHERE day LIKE '%day%'
OR month LIKE '%month%'
OR year LIKE '%year%'

returns all records

Answer 3

Using OR instead of AND changes the logic of the query. It doesn't make the parameters optional. The way to go is not using the parameters you are not using. I don't know any other way.

Answer 4

I would make the default value for every parameter to %, when someone fill a parameter, I would pass %value%

Answer 5

do you mean passing % in as if it were a value?

Answer 6

Why don't you create your query dynamically? Depending on the parameters you have , append the filters dynamically.

eg:

string query = "SELECT * FROM film"; string paramenters = string.empty;

if(day!= string.empty) parameters = " Where day LIKE '%day%'";

if(month != string.empty) { if(parameters != string.empty) parameters += "AND month LIKE '%month%'"; else parameters = "WHERE month LIKE '%month%'"; }

and so on....

In this case you wont get extra results that you will get with 'OR'

Answer 7

something like

 function queryData(year,month,day) 

 declare Y

 if year == nothing
     Y = '%'
 else
     Y = '%' + year + '%'

 declare M

 if month == nothing
     M = '%'
 else
     M = '%' + month + '%'

 declare D

 if day == nothing
     D = '%'
 else
     D = '%' + day + '%'

 return result of :

 SELECT * FROM film
 WHERE day LIKE D
 OR month LIKE M
 OR year LIKE Y

Answer 8

I've not tested, but your query looks like it should work as long as you make sure that the unused parameters are empty strings. e.g.

day = ""
month = "dec"
year = "2008"

would make your query:

SELECT * FROM film
WHERE day LIKE '%%'
AND month LIKE '%dec%'
AND year LIKE '%2008%'

'%%' should match any string as long as it's not NULL.

Answer 9

thanks for your help folks I ended implementing the following. works a treat

if year == nothing
   Y = ''
else
   Y = '%' + year + '%'