I have 3 tables, COMPONENT, CHASSISHISTORY and COMPONENTHISTORY which together determine the distance travelled by a component and the sessions it was involved in.
In COMPONENT I have the fields ID, LEFT and NEW where
ID is the database ID of the component
LEFT is the distance remaining on the component
NEW is the original value for distance
So the component has run NEW - LEFT miles.
The component history has fields ID which is the database table ID, COMPONENTID which links to the entry in the COMPONENT Table and CHASSISHISTORY_ID which links to the CHASSISHISTORY table. CHASSISHISTORY holds the distance run by the chassis for a specific session as DISTANCERUN
SO - by summing DISTANCERUN for a specific component I should get the total distance run for the component which in turn should be the same as NEW - LEFT in the component table.
I am looking for errors however so need a query which will list the component ID for each component where these 2 distances do not equal each other but am having problems trying to work it out
So far I have got as far as
SELECT icomponent.icomponent_id, SUM(CHASHIST.ichassishistory_distance) AS SESSTOTAL
LEFT JOIN icomponenthistory COMPHIST ON COMPHIST.icomponent_id = icomponent.icomponent_id
LEFT JOIN ichassishistory CHASHIST ON CHASHIST.ichassishistory_id = COMPHIST.ichassishistory_id
GROUP BY icomponent.icomponent_id
ORDER BY icomponent.icomponent_id
This gives me the total for each component as obtained from the CHASSISHISTORY table but I can't see how to also get tyhe component table total in the same query and hopefully how to see if these differ.