| Author |
Topic |
|
Naqibullah
Starting Member
7 Posts |
 Posted - 2013-02-24 : 23:14:51
|
| hi friends hope you are doing well...i have a problem related to a query retrieving data from three tables...i have three tables i.e. Member,loan, repayment each member takes loan and this loan will be paid in a number of installments, and now my problem is assume i have a member who has taken 6000 Rs loan and she paid the mentioned loan in three installments i.e 2000 each installments when i try to retrieve the the loan i.e 6000 and the repayments i.e 2000 three times. the query returns the loan 6000 per each installment 2000 that is totall loan will be 18000 which is not true and if i make a temporary relation between loan and repayment table then only 6000 loan with one installment 2000 will be retrieved |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-24 : 23:32:20
|
you just need to group the repayment data first before joining to loanslikeSELECT m.*,l.loanid,r.total FROM member mJOIN loan lON l.memberid = m.memberidJOIN (SELECT loanid, SUM(repayment) AS Total FROM repayment GROUP BY loanid )rON r.loanid = l.loanid I've assumed column names above so make sure you use actual column names instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Naqibullah
Starting Member
7 Posts |
Posted - 2013-02-25 : 22:24:52
|
| thanks for the ans but let me elaborate the relationship among my tables there is one to many relationship between member and loan, one to many relationship between member and repayment but there is no relationship between loan and repayment i want to run a query to retrieve data of member from loan and repayment i.e list of loan taken and list of repayment given in one page so that i can have total loan and total repayment and therefore i will be able to calculate outstanding balance from that query result |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Naqibullah
Starting Member
7 Posts |
Posted - 2013-02-26 : 03:19:35
|
the data that i want to retrieve should be as followMem_ID Mem_Name loan_date loan_amnt Rep_date Rep_amnt001 Bob 12/01/12 10000 26/01/12 5000001 Bob 12/02/12 3000001 Bob 30/01/12 2000as i explained my table relationship before please copy the above table in a editor so you can see what type of format i wantquote:
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 23:38:20
|
quote:
Originally posted by Naqibullah
the data that i want to retrieve should be as followMem_ID Mem_Name loan_date loan_amnt Rep_date Rep_amnt001 Bob 12/01/12 10000 26/01/12 5000001 Bob 12/02/12 3000001 Bob 30/01/12 2000as i explained my table relationship before please copy the above table in a editor so you can see what type of format i wantquote:
what if there are more than one loans /repaymentsShow us your complete scenario as per below formatit should be easily consumable format using create table and insert statements as shown in below linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Naqibullah
Starting Member
7 Posts |
Posted - 2013-02-27 : 03:39:29
|
[code][/code]SELECT Woman.Name, Loan.Loan_amnt, Loan.Loan_date, Repayment.Fixed_amnt, Repayment.Rep_dateFROM (Woman INNER JOIN Loan ON Woman.Mem_ID = Loan.Mem_ID) INNER JOIN Repayment ON Woman.Mem_ID = Repayment.Mem_ID;[code][/code] quote:
But this query retrieves same loan_amnt or fixed_amnt multiple timesas i explained my table relationship again want to tell: woman to loan one to many, woman to repayment one to many but there is no relationship between loan and repayment... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
SQL query in access