RE: Today's SQL Brain teaser

[David FOXWELL <David.FOXWELL@xxxxxxxxx>]

Peter,

Sorry about my error I've corrected it. Sorry also about the names, I translated the French. I've tried your solution but your temptable only returns two values, 5 and 36. For each line in table 2, I need to be able to test if the No is between Start_x and end_x of all lines in table1.
> > Table1
> > Start_x    end_x
> > 5            10
> > 12            16
> > 25            36
> >
> >
> > Table2
> > Id    No
> > ID1    7
> > ID2    16
> > ID3     1


Hold on have I just answered my own question ? Join Table1 to table2 and select lines where No BETWEEN START_x and End_x. Will try that!



> -----Message d'origine-----
> De : midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Colpaert, Peter
> Envoyé : jeudi 10 septembre 2009 11:58
> À : Midrange Systems Technical Discussion
> Objet : RE: Today's SQL Brain teaser
>
> David,
>
> This should work:
>
> with temptable (start_min, end_max) as ( select min(start_x),
> max(end_x) from table1) select * from table2 where exists (select 1
>                         from temptable
>                         where no_nbr between start_min and end_max);
>
> However, your question is wrong: ID1 is not retrieved because
> 4 is not between 5 and 36 ;-)
>
> Also, I had to change the field names because "start", "end"
> and "no" are reserved words.
>
> Hope this helps.
>
> Best regards,
>
> Peter Colpaert
> Software Engineer, Philips Consumer Luminaires
>
> Industrieterrein Satenrozen 11, 2550 Kontich, Belgium Tel.
> +32/ 3 450 74 09, Fax +32/ 3 450 74 33, Internal 1317
> peter.colpaert@xxxxxxxxxxx, www.philips.com
>
> Simply Switch to printing double-sided and printing less
>
>
>
>
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> > bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
> > Sent: donderdag 10 september 2009 11:41
> > To: Midrange Systems Technical Discussion
> > Subject: Today's SQL Brain teaser
> >
> > Hi,
> >
> > How can I extract ID1 and ID2 but not ID3 from the table2
> below? Their
> > numbers are between start and end of Table1
> >
> > Table1
> > Start    end
> > 5            10
> > 12            16
> > 25            36
> >
> >
> > Table2
> > Id    No
> > ID1    4
> > ID2    16
> > ID3     1
> >
> >
> > Thanks.
> > --
> > This is the Midrange Systems Technical Discussion
> (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
> please take
> > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.
>
>
> The information contained in this message may be confidential
> and legally protected under applicable law. The message is
> intended solely for the addressee(s). If you are not the
> intended recipient, you are hereby notified that any use,
> forwarding, dissemination, or reproduction of this message is
> strictly prohibited and may be unlawful. If you are not the
> intended recipient, please contact the sender by return
> e-mail and destroy all copies of the original message.
> --
> This is the Midrange Systems Technical Discussion
> (MIDRANGE-L) mailing list To post a message email:
> MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
> list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
> please take a moment to review the archives at
> http://archive.midrange.com/midrange-l.