Re: SQL to find a range within a range

bay_dar_at_yahoo.com wrote:
> SQL to find a range within a range.
>
>
> I have customers who have signed up for a start and end ranges. Lets
> call them cust_start and cust_end. On our side we will generate ranges
> and look for customers who fall within these ranges. Lets call them
> our_start, our_end ranges. I'm having trouble writing a Query to
> select all the possibilities. I'm running Oracle 10.x
>
> For instance We have:
>
> Cust_start = 10
> Cust_end = 20
>
> Our_start our_end is in the range
> 10 20 yes
> 9 10 yes
> 9 11 yes
> 9 21 yes
> 11 19 yes
> 19 21 yes
> 8 9 no
> 21 22 no
>
> I tried the following, but the last two statements don't seem to
> work. Any advice would be appreciated.
>
> SELECT *
> FROM cust.members
> WHERE (
> (members.start BETWEEN 19 AND 21) OR
> (members.end BETWEEN 19 AND 21) OR
> (19 BETWEEN members.start AND members.end) OR
> (21 BETWEEN members.start AND members.end) )

So why did you add those last two conditions? they add nothing. Shouldn't
 SELECT *
 FROM cust.members
  WHERE

     (members.start BETWEEN 19 AND 21) OR
     (members.end BETWEEN 19 AND 21)         ;

HTH,
  Ed Received on Thu Sep 14 2006 - 14:13:10 CDT