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!