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.