This is the solution that I was trying to avoid:
SELECT crsTest.Fld1, SUM(crsTest1.Amount) AS Amount1, 0000000000.00 AS Amount2;
FROM crsTest;
INNER JOIN crsTest1 ON crsTest.Fld1 = crsTest1.Fld1;
GROUP BY 1;
UNION ;
SELECT crsTest.Fld1, 0000000000.00 AS Amount1, SUM(crsTest2.Amount) AS Amount2;
FROM crsTest;
INNER JOIN crsTest2 ON crsTest.Fld1 = crsTest2.Fld1;
GROUP BY 1;
ORDER BY 1 ;
INTO CURSOR crsResult
SELECT Fld1, SUM(Amount1) AS Amount1, SUM(Amount2) AS Amount2;
GROUP BY 1;
FROM crsResult
Thanks.
>You got exactly what you ask for :-)
>The doubled result in the result is because the recordset looks like this (Lets change the second 10 in the crsTest1 to 11):
>
>Fld1 Test1.Ammount Test2.Ammount
>---------------------------------------
>1 10 100
>1 11 100
>
>You got as many records as the largest number of records in all tables. In VFP9 you could use derived tables to got the result you want, I am not sure about VFP8 (can't test it right now).
>Here VFP9 query:
>
>SELECT crsTest.Fld1, cTest1.Amnt AS Ammount1, cTest2.Amnt AS Ammount2;
> FROM crsTest;
>INNER JOIN (SELECT Fld1, SUM(Ammount) AS Amnt;
> FROM crsTest1;
> GROUP BY Fld1) cTest1;
>ON crsTest.Fld1 = cTest1.Fld1;
>INNER JOIN (SELECT Fld1, SUM(Ammount) AS Amnt;
> FROM crsTest2;
> GROUP BY Fld1) cTest2;
>ON crsTest.Fld1 = cTest2.Fld1
>
>
>If this didn't works in VFP8, you could first query second two tables and then join the results to main one.