AW: Subselect with between

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Roger Harman
Gesendet: Wednesday, 14. October 2009 02:49
An: midrange-l@xxxxxxxxxxxx
Betreff: 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.