Getting closest matching values

I apologise if this is a stupid question, but I would appreciated any
help on this subject.

I want to create a view (VIEW_1 in example below) where I take numeric
values from a field in one table (DEPTH_FROM in TABLE_1) and find the
closest matching values from another field in another table (DEPTH_AT
in TABLE_2) - the higher matching value and the lower matching value.


TABLE_1
BH_ID    DEPTH_FROM
B1                 10.6
B1                 14.2
B1                 16.1
B1                 17.0

TABLE_2
BH_ID       DEPTH_AT
B1                  9
B1                 10
B1                 11
B1                 12
B1                 13
B1                 14
B1                 15
B1                 16
B1                 17
B1                 18

VIEW_1
BH_ID         DEPTH_FROM     DEPTH_LOWER         DEPTH_HIGHER
B1                       10.6                    10
11
B1                       14.2                    14
15
B1                       16.1                    16
17
B1                       17.0                    17
17

Any assistance would be appreciated.

Regards,
Hennie

Should give you what you want


CREATE TABLE TABLE_1(BH_ID CHAR(2), DEPTH_FROM DECIMAL(5,1))

INSERT INTO  TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',10.6)
INSERT INTO  TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',14.2)
INSERT INTO  TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',16.1)
INSERT INTO  TABLE_1(BH_ID,DEPTH_
FROM) VALUES('B1',17.0)


CREATE TABLE TABLE_2(BH_ID CHAR(2), DEPTH_AT DECIMAL(5,1))

INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',9)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',10)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',11)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',12)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',13)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',14)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',15)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',16)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',17)
INSERT INTO  TABLE_2(BH_ID,DEPTH_
AT) VALUES('B1',18)

GO

CREATE VIEW DEPTH_RANGES
AS
SELECT a.BH_ID,
a.DEPTH_AT AS DEPTH_LOWER,
b.DEPTH_AT AS DEPTH_HIGHER
FROM TABLE_2 a
INNER JOIN  TABLE_2 b ON b.BH_ID=a.BH_ID
AND b.DEPTH_AT=(SELECT MIN(c.DEPTH_AT)
FROM TABLE_2 c
WHERE c.BH_ID=a.BH_ID
AND c.DEPTH_AT>a.DEPTH_AT)

GO

CREATE VIEW VIEW_1
AS
SELECT a.BH_ID,
b.DEPTH_LOWER,
a.DEPTH_FROM,
b.DEPTH_HIGHER
FROM TABLE_1 a
INNER JOIN  DEPTH_RANGES b ON b.BH_ID=a.BH_ID
AND a.DEPTH_FROM >= b.DEPTH_LOWER
AND a.DEPTH_FROM < b.DEPTH_HIGHER
GO

Thanks Mark!

This helps a lot!

Regards,
Hennie