RE: Today's SQL Brain teaser

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Colpaert, Peter
Envoyé : jeudi 10 septembre 2009 14:01
À : Midrange Systems Technical Discussion
Objet : 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.
--
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.