RE: Subselect with between

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

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.