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.