Rich,
Are you sure that that is what you want ?
Suppose your range values were something like :
begin end
1 9
15 19
23 29
ie, the RANGE table shows that 10-14 and 20-22 are invalid ("not allowed")
values.
Your problem statement and the SQL that Paul provides for the problem
statement
would return numbers like 10, 11, 20,21 which are, actually, invalid.
You'd have to write a cursor to loop through the valid ranges ??
Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd
Paul Baumgartel <[EMAIL PROTECTED]> 22/02/2002 12:43 PM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)
Subject: Re: sql question
To use your example column names:
select num from numbers where num between
(select min(begin) from range) and (select max(end) from range);
--- oracle dba <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I have a SQL question. Suppose I have a table called RANGE looks
> like
> this:
>
> begin end
> 1 9
> 10 19
> 20 29
>
> Then I have a table NUMBERS that's full of bunch of numbers like
> this:
>
> num
> 1
> 2
> 3
> 4
> ...
> 98
> 99
> 100
>
> I want to write a SQL that returns the number that are within
> the ranges defined in the RANGE table. So number 1 through 29
> should be returned.
>
> Can someone help me with this? Thanks.
>
> Rich
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: oracle dba
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Baumgartel
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
