| 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 loans
like
SELECT m.*,l.loanid,r.total
FROM member m
JOIN loan l
ON l.memberid = m.memberid
JOIN (SELECT loanid, SUM(repayment) AS Total
FROM repayment
GROUP BY loanid
)r
ON r.loanid = l.loanid
I've assumed column names above so make sure you use actual column names instead
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 follow
Mem_ID Mem_Name loan_date loan_amnt Rep_date Rep_amnt
001 Bob 12/01/12 10000 26/01/12 5000
001 Bob 12/02/12 3000
001 Bob 30/01/12 2000
as 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 follow
Mem_ID Mem_Name loan_date loan_amnt Rep_date Rep_amnt
001 Bob 12/01/12 10000 26/01/12 5000
001 Bob 12/02/12 3000
001 Bob 30/01/12 2000
as 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 /repayments
Show us your complete scenario as per below format
it should be easily consumable format using create table and insert statements as shown in below link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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_date
FROM (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 times
as 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