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!