Peter Haworth <p...@mollysrevenge.com> wrote: > Trying to implement the following situation involving 4 tables > > Customers is the "master table" in that the results should end up with > one row for each primary key value in it. > > I need to sum the values of a column in the Sales table, which has a > column that joins to the primary key of Customers and can have > multiple rows for each Customers primary key value. > > I also need to sum the values of a column in the Expenses table. In > order to get the Expenses entries for each primary key value in > TableA, I first have to join to the Transactions table using the > primary key of Table A, and then link to Expenses using the primary > key value of Transactions. Transactions can have multiple rows for > each Customers primaryKey value and Expenses can have multiple rows > for each Transactions primary key value. > > Here's the latest attempt. > > SELECT Customers.CustID,sum(Sales.value) AS totalsales, > Sum(Expenses.value) AS totalexpenses > FROM Customers > LEFT JOIN Sales ON Sales.CustID=Customers.CustID > LEFT JOIN Transactions ON Transactions.CustID=Customers.CustID > LEFT JOIN Expenses ON Expenses.TranID=Transactions.TranID > GROUP BY Customers.CustID
select Customers.CustID, (select sum(value) from Sales where Sales.CustID=Customers.CustID), (select sum(value) from Transactions join Expenses on (Expenses.TranID=Transactions.TranID) where Transactions.CustID=Customers.CustID) from Customers; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users