>How would you select a sum of fields from 2 child tables for the parent key?
>
>Here is the pseudo code of what I need:
>
>select (sum(hrs*rate) from Labor where Labor.ifkey=Job.ipkey) + (sum (qty*price) from Material where Material.ifkey=Job.ipey) as TotalCost
Using derived tables (VFP9 only), of course.
select cast(NVL(LaborTotal,0) + nvl(MateralTotal,0) as N(10,2)) as Total
from (select IfKey, sum(hrs*rate) as LaborTotal from Labor group by 1) Labor
FULL JOIN (select ifKey, sum(qty*Price) as MaterialPrice from Material group by 1) Material on Labor.ifkey = Material.ifkey
If it's not broken, fix it until it is.
My Blog 
