General information
Category:
Coding, syntax & commands
Title:
Getting rid of SQL Select duplicates
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only