0

Hi Everyone,

I got a problem while creating a views in sql server 2005. In my database there are several tables which are
accounts_transaction
bill_info
member_bill
member_info
relationship is exist between “member_info and accounts_transaction, member_info and member_bill, bill_info and member_bill” but there is not any relationship between bill_info and accounts_transaction.
Now I would like to create views to show member information along with their total bill stored in bill_info and member_bill, total payment stored in accounts_transaction. Therefore I have written the following sql query but these does not show me actual output of total bill and total payment due to not having relationship between accounts_transactiona and bill_info.

SQL Query:
select member_info.member_id, sum(bill_info.bill_amount) as billamount,
sum(accounts_transaction.credit_amount) as payamount,
sum(bill_info.bill_amount) - sum(accounts_transaction.credit_amount) as duesamount
from member_info, bill_info, member_bill, accounts_transaction
where member_info.member_id = accounts_transaction.member_id
and member_info.member_id = member_bill.member_id
and bill_info.bill_id = member_bill.bill_id
group by member_info.member_id

AND

SELECT dbo.member_info.member_id, SUM(dbo.bill_info.bill_amount) AS billamount, SUM(dbo.accounts_transaction.credit_amount) AS payamount,
SUM(dbo.bill_info.bill_amount) - SUM(dbo.accounts_transaction.credit_amount) AS duesamount
FROM dbo.member_info INNER JOIN
dbo.accounts_transaction ON dbo.member_info.member_id = dbo.accounts_transaction.member_id INNER JOIN
dbo.member_bill ON dbo.member_info.member_id = dbo.member_bill.member_id INNER JOIN
dbo.bill_info ON dbo.member_bill.bill_id = dbo.bill_info.bill_id
GROUP BY dbo.member_info.member_id


Please help me to find a good solution. If you need my full ERD please let me know at once.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by huangzhi
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.