BETWEEN a rock AND a hard place

  • Thread starter Thread starter cinnie
  • Start date Start date
C

cinnie

hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are
inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the
conditions... 10 <= Value <= 20). But there are times, especially when
working with intervals, when I might want non-overlapping values like,
say... 10 < Value <= 20, then 20 < Value <= 30, etc. This is easy to do
using... WHERE Value > 10 And Value <= 20 instead of BETWEEN...AND, but it
gets harder when we don't know which of the limits is the lower bound and
which is the upper bound. For example...

SELECT tData.Country
FROM tData
WHERE tData.GDP BETWEEN (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya');

Now all of you gurus probably know which of these limits is the lower one
and which is the upper, but I don't! If I wanted, say, GDP values that
were... lower bound <= Value < upper bound, I would have to go through
more SQL contortions to get the desired result.

What I'd like to see is an optional phrase that could be attached to the
current BETWEEN...AND operator to make this sort of thing easier, say
something like...

BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit) or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)


Any thoughts? (or am I all wet?)
 
Using the between it does not matter which is the higher or lower. It works
the same.
For your EXCLUSION use NOT Between.
 
cinnie said:
hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results
that are inclusive at both ends. (BETWEEN 10 and 20 gives values
meeting the conditions... 10 <= Value <= 20). But there are
times, especially when working with intervals, when I might want
non-overlapping values like, say... 10 < Value <= 20, then 20 <
Value <= 30, etc. This is easy to do using... WHERE Value > 10
And Value <= 20 instead of BETWEEN...AND
Click to expand...

It can still be done with BETWEEN. For example, if your data was
Integer:
BETWEEN 11 AND 20
, but it gets harder when we
don't know which of the limits is the lower bound and which is the
upper bound. For example...
Click to expand...

And that's only because Access does not force you to put the operands in
the correct order the way other sql dialects do.
BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit)
or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)


Any thoughts? (or am I all wet?)
Click to expand...
I guess it would be nice to have, but we don't have the power to make it
happen. It's been quite a while since JetSQL was updated, but, you could
always try submitting this request to MS. I suspect they would reject
the request as it's not per SQL standards.

As to your problem, I would use VBA to solve it.
Assign to the GDP from both countries to variables, then assign the
lower of the two to a variable called lowlimit and the higher to a
variable called highlimit, then pass those as parameters to your sql
statement, increasing the value of the low limit if you want it excluded
or vice versa with the high limit.
 
When you want to use (> and <) or (>= and <=) but you don't know which one
is lower or greater, the two possible SQL contortions that I see would be
first to simply to use *both* cases:

WHERE (tData.GDP > (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND tData.GDP < (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya')
)

OR (tData.GDP > (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya')
AND tData.GDP < (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
)

The 'wrong' case will return no result; so you get what you want. However,
a better solution would be to use the Min() and Max() functions to retrieve
the required result for both subqueries (untested):

WHERE tData.GDP > (SELECT Min (tData.GDP)
FROM tData
WHERE tData.Country in ('Nigeria',
'Kenya'))
AND tData.GDP < (SELECT Max (tData.GDP)
FROM tData
WHERE tData.Country in ('Nigeria',
'Kenya'))


This way, you see that you don't really need to use an optional phrase or
keyword.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Back
Top