left join etat on r1.ForeignKey= etat.PrimaryKey



or something like that


othrwise I missundertand your question :-)

P

>perhaps ...
>
>where in the sql clause ?
>
>
>>wouldn't taht be a LEFT join??
>>
>>P
>>
>>
>>>hi Boris,
>>>
>>>i have a little problem....
>>>
>>>if i want ALL R1.Cnomcustomer but also R1.Cnomcustomer without Etats...
>>>
>>>it's possible ?
>>>
>>>
>>>
>>>
>>>
>>>>Try this:
>>>>

>>>>SELECT R1.Cnomcustomer ,;
>>>>       R1.Iid ,;
>>>>       Cor9.IetatId  , ;
>>>>       Cor9.nType  ,;
>>>>       Cor9.ccat_Physiq , ;
>>>>       cor9.ddebut,;
>>>>       cor9.Ltransfert;
>>>>FROM Amline!Resident R1;
>>>>INNER JOIN (SELECT  Etats.iresidentId , ;
>>>>                    NVL( Etats.nType, 0 )         AS nType ,;
>>>>                    Etats.iid                     AS IetatId ,;
>>>>                    Etats.ccat_Physiq             AS ccat_Physiq,;
>>>>                    NVL( Etats.lTransfert , .F. ) AS lTransfert  ,;
>>>>                    Etats.ddebut                  AS ddebut;
>>>>            FROM Etats;
>>>>            INNER JOIN (SELECT IresidentId,;
>>>>                               MAX(IID) AS IId;
>>>>                        FROM Etats;
>>>>                        GROUP BY IresidentId) Etst ON Etats.IresidentId = Etst.IresidentId AND;
>>>>                                                      Etats.IId         = Etst.IID) Cor9 ;
>>>>       ON R1.iid = cor9.iresidentId ;
>>>>WHERE EMPTY( TTOD( R1.dsortie ) );
>>>>ORDER BY R1.Cnomcustomer;
>>>>INTO CURSOR temp
>>>>

>>>>
>>>>NOT TESTED!