General information
Category:
Bases de datos, tablas, vistas, índices y SQL
Título:
Multi Table View Problem
Miscellaneous
ID de la conversación:
00525660
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
Next
Responder
Ver el mapa de esta conversación
View the map of this thread starting from this message only
Ver todos los mensajes de esta conversación
Ver todos los mensajes de esta conversaicón a partir del actual