|
|
Posted by jmartineau on 11/22/05 22:16
Hello,
Here is a brief summary:
Table 1 = All Accounts
- with fields such as Customer ID and Account #
Table 2 = Deposit Balance Table
- with fields such as Account #, Balance
Table 3 = Loan Balance Table
- with fields such as Account #, Balance
All accounts are either deposit accounts or loan accounts. What I need
to do is to gather information about total balances in both deposits
and loans for each customer. I haven't been able to hit the right query
for doing this. I can easily get information about one or the other,
such as the following:
SELECT All_Accounts.Customer_ID, COUNT (DISTINCT
(Deposit_Balance_Table.Account_Number)), Sum
(Deposit_Balance_Table.Balance)
FROM Product_Table, Deposit_Balance
WHERE (Product_Table.Account_Number=Deposit_Balance.Account_Number)
GROUP BY Product_Table.Customer_ID ORDER BY 1
Which will give me one row for each user, and show me the total number
of deposit accounts each customer has and a sum of the balances in each
of those accounts. I can make a similar query involving Loan Accounts.
As soon as I try to draw both, however, I wind up below my depth.
Something to do with the handedness of my joins, I believe. Often I
will get one column of information (either deposits or loans), or the
query will fail because the join I'm attempting is invalid, etc. I need
to take every row in the All_Accounts table, match each one to its
balance in either the Deposit or Loan table, and then group them all by
the Customer ID and sum them, so that I can find out the total
relationship balance per customer. Any help would be appreciated.
Navigation:
[Reply to this message]
|