December 5, 2010 at 3:04 am
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.
December 5, 2010 at 3:22 am
Please post table def inclding foreign key refernces and some sample data as described in the first link in my signature. Also please post your expected output based on the sample data.
December 5, 2010 at 7:10 am
The two queries in your first post are functionally identical and show an indirect relationship between bill_info and accounts_transaction. Whilst the joins between the two tables are known, the cardinality between the tables is not, and this is where you are having problems. Unfortunately this isn't something which can be guessed, which is what you are asking without any sample data. Here's a suggestion which should be quick to test: rather than joining up all the tables then aggregating and hoping it will work, try aggregating bill_info and account_transaction separately as derived tables, something like this:
SELECT
mi.member_id,
bi.billamount,
at.payamount,
bi.billamount - at.payamount AS duesamount
FROM dbo.member_info mi
INNER JOIN dbo.member_bill mb ON mi.member_id = mb.member_id
INNER JOIN (
SELECT member_id,
payamount = SUM(credit_amount)
FROM dbo.accounts_transaction
GROUP BY member_id
) at ON mi.member_id = t.member_id
INNER JOIN (
SELECT bill_id,
billamount = SUM(bill_amount)
FROM dbo.bill_info
GROUP BY bill_id
) bi ON mb.bill_id = bi.bill_id
Note the use of table aliases - it makes your code far easier to read and digest.
Note also that in the absence of sample data this is untested. Follow Lutz' suggestion for tested code.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply