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.
midrange.com
