Linking data between tables - Please help

D

DavidC

Can anyone tell me how to link 2 tables where the data is not exactly equal
e.g table one looks like
column 1 column 2
2.05 x1
2.10 x2
2.15 x3
2.20 x4 etc.
and table two looks like
column 1
2.07
2.08
2.19 etc

How do I link these tables in order, for example, for x1 to be the result
when any value between 2.05 and 2.10, x3 when 2.19 is selected etc.?

I know Im not explaining this well but any pointers would be really
appreciated...thanks
 
D

Douglas J. Steele

You can determine the appropriate row in Table1 using a comparison like:

SELECT MAX(Column1)
FROM Table1
WHERE Table1.Column1 <= Value

You can then get the appropriate value of Column2 by using a correlated
subquery based on the above:

SELECT Column2
FROM Table1
WHERE Table1.Column1 =
(SELECT MAX(Column1)
FROM Table1
WHERE Table1.Column1 <= Value)

To combine Table1 and Table2, try something like:

SELECT T.Column1,
(SELECT Column2
FROM Table1
WHERE Table1.Column1 =
(SELECT MAX(Column1)
FROM Table1
WHERE Table1.Column1 <= T.Column1)) AS LookupValue
FROM Table2 AS T
 
Top