Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

searching BETWEEN values?

 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about something like this?
 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic