This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes searching BETWEEN values? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "searching BETWEEN values?" Watch "searching BETWEEN values?" New topic
Author

searching BETWEEN values?

Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

How do people implement those searches that ask for min and max values? I can do the sql for between. But the problem comes when 1 field is left blank. 1 null spoils the whole equation.
How do you handle something like this
SELECT * FROM table WHERE name BETWEEN x AND y;
What happens when x is null, or y is null? Do I have to have 3 sql statements? 1 for each possibility? THis is a common search so I assume this problem has been solved. Whats the answer??
Thanks!
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
How about something like this?


"I'm not back." - Bill Harding, Twister
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

You understand that x and y are the variables the user is entering?
That looks interesting and I think I will give it a shot! Can't use BETWEEN but who cares. I'll report back after I exercise this.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

JBOSSQL does not seem to allow the x or y input values being null. I have never seen a comparison for nullness on input values either, is this legal?
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

ok, I got it now. it was simpler than I thought.

SELECT * FROM table where
((x = '') OR (x <> '' AND firstName >= x)) AND
((y = '') OR (y <> '' AND firstName <= y)) AND etc...
I shouldn't use null values, just use empty strings. That's probably why everyone that answered the question answered as you did.
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
You understand that x and y are the variables the user is entering?
Well no, I wasn't really sure about that, or if the statement would be legal if that were the case - just trying ideas. Glad to see something vaguely similar worked out...
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: searching BETWEEN values?
 
Similar Threads
for loop vs while loop
Set to null...
Recursive number problem
how to compare the values in arrays
initialize database from properties file