>Consider 3 entities in a grandparent parent child relationship:
>
>House Airway Bill (HAWB) grandparent
>Shipments - parent
>Charges - child
>
>A HAWB can contain multiple shipments and multiple charges can be posted to a shipment.
>
>What is required is a table by HAWB with the total values of the shipments and the total of the charges.
>
>Can one do this in 1 SQL?
>
>select HAWB, sum(shipment.value), sum(charges.amount);
>from Shipment,Charges;
>where shipment.shipment= charges.shipment;
>order by Hawb;
>group by Hawb
>
>gives faulty shipment values. The value gets multiplied by the number of charges for each shipment.
>
>Is this a classic, or worse a faq? In native VFP, you can solve the problem with cascated SQLs, but what if you are in an C/S environment? Just curious.
>
>TIA,
>
>Marc

Marc,

I think that the problem you're having is caused by the lack of a statement to qualify the relationship between the HAWB (grandparent) and the parent table. Possibly something like:

SELECT Hawb.shipment, SUM(Shipment.value), SUM(Charges.amount);
FROM Hawb, Shipment, Charges;
WHERE (Shipment.shipment = Hawb.shipment) AND;
(Charges.shipment = Shipment.shipment);
ORDER BY Hawb.shipment;
GROUP BY Hawb.shipment

I'm not sure I completely understand the structure of HAWB or what relates it to the parent table, but I think you get the idea.

hth,

George