A SELECT request

Assume we have two tables:
T1 T2

F1 F2 Field1 Field2
A A1 A2 Fred
A A2 B0 Judy
B B0 B1 Max
B B2 B3 Joe
B B3
I need to write a SELECT to return:
- Exact data from Field1 which matches F2; i.e. A2 (from Field1) for A2 (from F2)
- Or the closest smaller one; i.e. B1 (from Field1) for B2 (from F2)
Any help is highly appreciated.

Hi,
You can try something like this, assuming there will always just be one character at the beginning of the data and the rest is an integer value:
select F2
, (
select top 1
Field1
from T2
where left(T2.Field1,1) = left(T1.F2)
where convert(int,substring(T2.Field1,2,len(T2.Field1))) <= convert(int,substring(T1.F2,2,len(T1.F2)))
order by convert(int,substring(T2.Field1,2,len(T2.Field1))) desc
)
from T1
You might have to modify this based on your data...

Thanks for the post. however, here is a clean short resolution.
SELECT A.F1, A.F2, B.Field1, B.Field2
FROM @T1 A, @T2 B
where B.field1 = (select max(t2.field1) from @t2 as t2 where t2.field1 <= A.f2)