Hi Sergey,
I've used this to step thing.
But after a while a remembered that I need to set the ones not referenced in the table with the max() to a default value.

I in my stupidity remebered on OUTER JOIN condition and NVL() doing something like

*Say 'X' is a default

SELECT;
 Work_Table.Code, <b>NVL(</b>MAX(Ref_Table.Code)<b>,'X')</b> as max_code;
 FROM Work_Table ;
 <b>LEFT OUTER</b> JOIN Ref_Table ;
 ON Work_Table.Code = STRTRAN(Ref_Table.Code, " ", "") ;
 GROUP BY 1 ;
 INTO CURSOR temp1

This gives me the pour nonsense.

For common I never relate tables on characters, all my tables use an integer index.
But those people which bore a programmer with wishes need something like the code before.
So I start to test with simple data. This without MAX() for better understanding.
Since I have no idea how to post a programm as an attachment:
Please run:

SET ANSI OFF
SET EXACT OFF
CREATE TABLE Test1 (c1 c(5))
INSERT INTO Test1 (c1) VALUES ('abwa')
INSERT INTO Test1 (c1) VALUES ('ahoi')
INSERT INTO Test1 (c1) VALUES ('grau')
INSERT INTO Test1 (c1) VALUES ('will')
INSERT INTO Test1 (c1) VALUES ('u')
CREATE TABLE Test2 (c2 c(5))
INSERT INTO Test2 (c2) VALUES ('ab')
INSERT INTO Test2 (c2) VALUES ('a')
INSERT INTO Test2 (c2) VALUES ('d')
INSERT INTO Test2 (c2) VALUES ('willi')
INSERT INTO Test2 (c2) VALUES ('u')

SELECT c1,c2;
 FROM Test1;
 INNER JOIN Test2;
 ON c1=c2

*_TALLY=1

SELECT c1,c2;
 FROM Test1;
 INNER JOIN Test2;
 ON ALLTRIM(c1)=ALLTRIM(c2)

*_TALLY=2
*now the opposit way

SELECT c1,c2;
 FROM Test2;
 INNER JOIN Test1;
 ON ALLTRIM(c1)=ALLTRIM(c2)

*_TALLY=4? oops!

*Now all from Test1 with related test2
*OR .NULL.

SELECT c1,c2;
 FROM Test1;
 LEFT OUTER JOIN Test2;
 ON ALLTRIM(c1)=ALLTRIM(c2)

*oh oh- where are my relations?

*Now all from Test2

SELECT c1,c2;
 FROM Test1;
 RIGHT OUTER JOIN Test2;
 ON ALLTRIM(c1)=ALLTRIM(c2)

*closer, but also wrong
*next test may by link all and then filter?

SELECT c1,c2;
 FROM Test1;
 FULL JOIN Test2;
 ON ALLTRIM(c1)=ALLTRIM(c2);
 WHERE !ISNULL(c1)

*Am I stupid?
*maybe build all up (o.k. it's nonsense in real world there is this 2GB File limit) and then filter?

SELECT c1,c2;
 FROM Test1;
 FULL JOIN Test2;
 ON .T.;
 WHERE !ISNULL(c1) AND ALLTRIM(c1)=ALLTRIM(c2);

*No No I've wrote NOT ISNULL(C1)!

So none of the statments beyond give me the answer I need:

C1		c2
----------
abwa	ab
abwa	a
ahoi	a
grau	.NULL.
will	.NULL.
u		u

Ok, I can do a construct like

SELECT c1,c2;
 FROM Test1;
 LEFT OUTER JOIN Test2;
 ON LEFT(c1,LEN(ALLTRIM(c2)))=c2

But this not realy a solution, this is nothing for rushmore, and I need to do this with some 100,000 records versus some hundred.
There is also the idea of using an UDF but I personaly think that an UDF within a SQL statement is a bad joke.
It means an huge number of UDF calls and foxpro is not realy fast in things like that.
(Thats why I dont like this LEFT() either) So what to do?

Is it right that SELECT SQL changes behaviour of the = operator depending on the order of INNER JOIN or the direction ouf OUTER JOIN?
I couldn't find a word about it in helpfile.

Thank you for help

Agnes