>I have the following SQL that sometimes gives me erroneous results:
>
>

>select GROUP_FLD, NVL(SUM(MyTable2.Hours),00000.00) as TotHrs,
>NVL(SUM(MyTable3.Cases),00000.00) as TotCases from MyTable1
>join MyTable2 on MyTable2.pk_fld = MyTable1.pk_fld
>join MyTable3 on MyTable3.pk_fld = MyTable1.pk_fld
>group by GROUP_FLD
>

>
>What happens is that if Table3 has 2 records and Table2 has 1
>records, total of 2 two records are selected before VFP starts to group and SUM() the records. And the SUM(MyTable2.Hours) shows duplicate number of hours (as it calculates based on two records). Whereas in fact it should be on 1 record.
>
>Is the only solution to the above problem to move the SUM(...) to SUBQUERIES?
>
>Thank you.

select GROUP_FLD, 
(SELECT NVL(SUM(Hours)),00000.00) FROM MyTable2 where pk_fld=MyTable1.pk_fld) as TotHrs, 
(SELECT NVL(SUM(Cases)),00000.00) FROM MyTable3 where pk_fld=MyTable1.pk_fld) as TotCases
from MyTable1 
group by GROUP_FLD