RE: Today's SQL Brain teaser

David,

If you want the values from table2 where the NO is between the start / end of any line in table1, then you can simply do this:

Select *

From table2 a

Where exists (select 1
From table1
Where a.no between start and end);

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 13:52
To: Midrange Systems Technical Discussion
Subject: RE: Today's SQL Brain teaser

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!

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.