>Dear all
>I have 3 table with below fields and recordes :
>
>

>Admision.dbf
>AdmCode – FileNum – OkXPha – XphaDate
>       1         -      10      -      .T.      -   88/01/01
>       2         -      11      -      .T.      -   88/01/02
>
>Drg_Main.dbf
>AdmCode –      Fee40     -     Fee41     -     Fee42
>      1         -      100          -         0        -        0
>      1         -      200          -         0        -        0
>      2         -      300          -         0        -        0
>      2         -      400          -         0        -        0
>      2         -      500          -         0        -        0
>     
>Drg_Main_Ret.dbf
>AdmCode –      Fee31
>      1         -       50  
>

>
>

Reza,

Try this SQL:

Select Adm.*, SumFee, SumRet from Admission Adm ;
LEFT JOIN (select Ad.AdmCode, SUM(Drg_Main.Fee40-Drg_Main.Fee41+Drg_Main.Fee42) AS 'SumFee' from Admission Ad LEFT JOIN Drg_Main  ;
      ON Ad.AdmCode  = Drg_Main.AdmCode where Ad.OkXPha = .T.  group by Ad.AdmCode) Drg1 on Adm.Adm_Code = Drg1.Adm_Code
LEFT JOIN ;
(select Ad.AdmCode, SUM(Drg_Main_Ret.Fee31) AS 'SumRet' from Admission Ad ;
LEFT JOIN Drg_Main_Ret ON Ad.AdmCode = Drg_Main_Ret.AdmCode where Ad.OkXPha = .T. group by Ad.AdmCode) Drg2 ;
on Adm.Adm_Code = Drg2.Adm_Code where Adm.OkXPha = .T. ORDER BY Adm.XPhaDate,Adm.AdmCode

The idea here is to use JOINS with each table as a separate derived table.