>All,
>
>I am writing a building inspection application.
>
>Each DISTICT has one or more BUILDINGS
>Each BUILDING has one or more ADDITIONS
>Each ADDITION has one or more INSPECTIONS
>Each INSPECTION has one or more INSPECTIONCOMPONENT
>Each INSPECTIONCOMPONENT has one or more INSPECTIONCOMPONENTITEM
>Each ITEM has one or more INSPECTIONCOMPONENTITEM
>
>I need to sum the extension of the INSPECTIONCOMPONENTITEM quantity and the ITEM unit cost for a specific building using the following:
>
>SELECT ;
> SUM(SFAInspectionComponentItem.iQuantity * SFAItem.yUnitCost) AS iTotal;
>FROM ;
> sfa!sfainspectioncomponent ;
> INNER JOIN sfa!sfainspectioncomponentitem;
> INNER JOIN sfa!sfaitem;
> INNER JOIN sfa!sfainspectioncomponent;
> INNER JOIN sfa!sfaaddition;
> INNER JOIN sfa!sfabuilding ;
> ON Sfabuilding.iid = Sfaaddition.ibldgid ;
> ON Sfaaddition.iid = Sfainspection.iadditionid ;
> ON Sfainspection.iid = Sfainspectioncomponent.iinspectionid ;
> ON Sfainspectioncomponent.iid = Sfainspectioncomponentitem.iinspectioncomponentid;
> ON Sfaitem.iid = Sfainspectioncomponentitem.icomponentitemid ;
>WHERE Sfabuilding.iid = ?vp_iBuildingId ;
>INTO CURSOR REX
>
>Unfortunately the total is double what it should be. In my example there are 2 COMPONENTS for the INSPECTION and this seems to lead to a complete duplication of all the amounts.
>
>Since I am using a SUM, I cannot use DISTINCT is the SQL.
>
>How can I get the data not to duplicate?
>
>TIA
>
>Rex
Rex,
I mean couldn't see some of the relations :
SELECT ;
SUM(SFAInspectionComponentItem.iQuantity * SFAItem.yUnitCost) AS iTotal;
FROM ;
sfa!sfainspectioncomponent a ;
INNER JOIN sfa!sfainspectioncomponentitem b ;
ON a.iid = b.iinspectioncomponentid;
INNER JOIN sfa!sfaitem c ;
ON c.iid = b.icomponentitemid ;
INNER JOIN sfa!sfainspectioncomponent d ;
ON ?? ;
INNER JOIN sfa!sfaaddition e;
ON ?? ;
INNER JOIN sfa!sfabuilding f;
ON f.iid = e.ibldgid ;
WHERE Sfabuilding.iid = ?vp_iBuildingId ;
INTO CURSOR REX
Cetin