0

At a high-level this sounds trivial, but it turns out I've been scratching my head for a a couple of hours.

Situation:

I have table T, with columns a,b,c,d,e. Column a holds a string, while b,c,d,e each hold a boolean value.

I am allowing a user to perform a kind of search, where I prompt the user to enter values for a,b,c,d,e, and return all the rows where those values all match.

In a perfect world, the user enters all values (lets say a="JavaScript" , b="true", c="false", d="false", e="true") and a resulting query (In Scala, referencing a remote DB running MySQL) might look something like this:

connection.createStatement().executeQuery("SELECT * FROM T 
WHERE a = '" + a_input + "' 
and b = " + b_input + "
and c = " + c_input + "
and d = " + d_input + "
and e = " + e_input + ";")

Problem:

I give the user the option to 'loosen' the constraints, so it is possible that a_input="" and b_input="", etc... Potentially all fields a,b,c,d,e can be empty ("") If a field is omitted, it should not affect the resulting response. In other words, if c is not entered, the result can contain entries where c is TRUE or FALSE

Question:

How do I write ONE query that covers the situation where potentially all fields can be empty, or just some, or none?

Rodman Grenich
  • 11
  • 6

3 Answers3

0

you could use 

Select *
from table 
where  a in ('','othervalue','othervalue')
and b in ('','othervalue','morevalues')

and so on.....that is like using an or for each field and it will match even if it's empty

John
  • 1,711
  • 2
  • 29
  • 42
  • Thank you. If field1 is "", will it look for instances where field1 ="", or will it ignore field1 completely? If the former, this is not the functionality I need. I updated the Problem section in the OP to detail further what I need. – Rodman Grenich Dec 06 '16 at 14:46
  • It will match anyway, cause you need to fullfill ANY of those values for each field, is like using an OR ..... '' or othervalue' or 'morevalues', just try it and you will see it works – John Dec 06 '16 at 14:48
  • Perhaps I was misunderstood, but I will try this query and let you know if it works, thank you. – Rodman Grenich Dec 06 '16 at 14:55
  • nono, you were very clear. You want all the registries that may or may not contain any of the values and at the same time matches the values that the user filled. The IN clause is used in those situations where you have optional fields...... – John Dec 06 '16 at 15:01
  • It isn't working. If I supply an argument, it works like expected, but if it is left empty, it is not letting all of them go through. – Rodman Grenich Dec 06 '16 at 15:54
  • could you post the query in a pastebin a post the link?.....You must be doing it wrong – John Dec 06 '16 at 17:29
0

Just build the WHERE dynamically. Instead of always searching for c = 'whatever', only include c in the WHERE if the user supplied a value for it.

CptMisery
  • 612
  • 4
  • 15
  • How to do this in one query is the question without a ton of if() checking whether or not a value was supplied? – Rodman Grenich Dec 06 '16 at 15:34
  • The only other option is to use juergen d's answer in the main comment section above. I have no idea what Juan is trying to say – CptMisery Dec 06 '16 at 15:49
  • Juerguen's answer is the same as mine, the only difference is that I'm using the IN clause but the meaning is exactly the same... – John Dec 06 '16 at 17:31
  • @Juan No. Yours says to pull records where `a` is blank or something else. His is saying pull records where `a_input` is blank or `a = a_input`. – CptMisery Dec 06 '16 at 18:15
  • it's the same as saying where a IN ('','a_input') that will match any of the values.....I think this was just a missunderstanding but we wanted to say the same – John Dec 07 '16 at 14:07
  • With yours when the user doesn't supply a value for a_input the query will look like `WHERE a IN ('', '')`. The other suggestion (`WHERE (a = '$a_input' or '$a_input' = '')`) will make the query look like `WHERE (a = '' or '' = '')` – CptMisery Dec 08 '16 at 16:59
  • @RodmanGrenich, I think you should reconsider using a "ton" of `if`'s. It would reduce the amount of work the query has to do. If you build all the logic into the query, it will have to check the value of column A on every record even when you don't care what A is. – CptMisery Dec 08 '16 at 17:21
0

This is tricky because the DB contains booleans but the parms are strings. Are the parms always blank. 'true', or 'false'? If so try

(B_input=''
 Or (b_input=''true' and b)
   Or (b_input='false' and ((not b)))
Alan
  • 325
  • 2
  • 5