SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 SQL query in access
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Naqibullah
Starting Member

Afghanistan
4 Posts

Posted - 02/24/2013 :  23:14:51  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/24/2013 :  23:32:20  Show Profile  Reply with Quote
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/

Go to Top of Page

Naqibullah
Starting Member

Afghanistan
4 Posts

Posted - 02/25/2013 :  22:24:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/26/2013 :  01:26:31  Show Profile  Reply with Quote
show us the sample data from tables in consumable format and output you want out of it. Without that we cant understand your exact scenario

see link below on how to post data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Naqibullah
Starting Member

Afghanistan
4 Posts

Posted - 02/26/2013 :  03:19:35  Show Profile  Reply with Quote
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 want
quote:


Edited by - Naqibullah on 02/26/2013 03:25:09
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/26/2013 :  23:38:20  Show Profile  Reply with Quote
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 want
quote:




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/

Go to Top of Page

Naqibullah
Starting Member

Afghanistan
4 Posts

Posted - 02/27/2013 :  03:39:29  Show Profile  Reply with Quote

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;

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...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  04:04:10  Show Profile  Reply with Quote
refer the link and post data in required format if you want us to help you out

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.51 seconds. Powered By: Snitz Forums 2000