RE: Subselect with between

["Roger Harman" <RHarman@xxxxxxxxxx>]

Thanks Birgitta and Richard.  Both solutions worked.  AND, I learned about INTERSECT and that you can join using BETWEEN.  I've seen (but not used) INTERSECT before and I always thought JOIN had to be an equal condition.

BTW.... the JOIN was really FAST.....

> > > Hauser@xxxxxxxxxxxxxxx 10/13/2009 9:33:41 PM >>>
Hi,

you may try the following statement (which may be not the best one, but
should return what you want).

   select Num
      from Table1 join Table2 on Num >= Start
      Where Num between Start and End
Intersect
   Select Num 
      from Table1 join Table2 on Num <= End
      Where Num between Start and End
order By Num


Mit freundlichen Grüßen / Best regards

Birgitta Hauser



> > > casey_r@xxxxxxxxxxxxxxxx 10/13/2009 6:34:25 PM >>>
Try:

select *
from File1
inner join File2 on NUM between START and END

You will get duplicated numbers if any of the ranges in File2 overlap.

Hope this helps.

Richard



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Roger Harman
Sent: Tuesday, October 13, 2009 8:49 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Subselect with between

I have a file with individual sequence numbers.  I have another file
with a start and end sequence number that contains comments about that
range. 

Example:
  File1
    NUM:  11
    NUM:  12
    NUM:  65
    NUM:  71

  File2
     Start:  10     End:  50      Cmt: "Range 1 blah blah"
     Start:  71     End:  71      Cmt: "Range 2 blah blah"

I want to find the records in File1 that fall between START and END in
File2.  In this case -  11, 12, 71

Something like "Select * from File1 where NUM between (select START,
END from File2)" 

I've tried various SELECT statements but no luck.  Any suggestions?

Thanks.