Hi All,

I've got a little problem with SELECT SQL.

I have to tables, One that hold som keys and an other one where that keys has to be added.

The first looks like

Ref_Table:
Code Key
--------
abc   1
def   2
x     3
ab    4
xy    5
a     6

the second one

Work_Table:
Code
----
abfahrt 
abcxy   
defrost   
xy    
alocation     
xray     

I want to connect these tables in way of best fit

Result:
Code	   Key
--------------
abfahrt     4
abcxy       1 
defrost     2
xy          5
alocation   6  
xray        3

IF try to use a SELECT SQL with a syntax like the following:

SELECT;
 Work_Table.Code,Key_Table.Key,MAX(Ref_Table.Code);
 FROM Work_Table;
 INNER JOIN Ref_Table;
 ON Work_Table.Code=ALLTRIM(Ref_Table.Code);
 GROUP BY 1

The result is, that I get the correct Code from Ref_Table, and a wrong Key.

What's wrong with my statement?

Thanks for Help

Agnes