Getting closest matching values

[Hennie]

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

[markc600]

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

[Hennie]

Thanks Mark!

This helps a lot!

Regards,
Hennie