Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Multi Table View Problem
I am trying to create one view which sums orders and payments by customer. I have three tables; cust, orders and pay and they are linked by custid. All customers have at least one order and one payment.
I get incorrect results with the following code:
SELECT Cust.custid, Cust.custname, sum(orders.amt), sum(pay.payamt);
FROM cust ;
Inner Join orders on Cust.custid = Orders.custid ;
Inner Join pay on cust.custid = pay.custid;
GROUP BY Cust.custid
The following, however, does work correctly, but I want it in one view.
SELECT Cust.custid, Cust.custname, sum(orders.amt);
FROM cust ;
Inner Join orders on Cust.custid = Orders.custid ;
GROUP BY Cust.custid;
into Cursor temp
Select temp.*, sum(pay.payamt);
From Temp;
Inner Join pay on Temp.custid=pay.custid;
Group by Temp.custid
Can anyone explain what is going on and perhaps provide some help?
Thanks
Jay
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement