Can SQL handle a Parent Child query ?

[saustin]

Hi All,
Have a parent file with a field (BASEID) that is also in a child (transaction) file many times. All records in both tables also have cost fields. What i am interested in is a case where the parent and child with the SAME BASEID do NOT have the same total costs. How do you do this in access sql ?
It's very easy to come up with the sum of the costs for several child records with the same BASEID. The problem is that an IN statement can only return one value. I need baseid and costs.
Any sql thoughts greatly appreciated ! Have looked at subquery & union help and am not sure where to go other than creating a temp table. There has to be a better way.

Thanks, Steve.

 

[ldandy]

Does the query below solve the problem?

SELECT PARENT.BASEID, First(PARENT.COST) AS ParentCost, Sum(CHILD.COST) AS ChildCost FROM PARENT INNER JOIN CHILD ON PARENT.BASEID = CHILD.BASEID
GROUP BY PARENT.BASEID
HAVING First(PARENT.COST) <> Sum(CHILD.COST)