The LEFT OUTER join is the lynchpin to your problem. Use a LEFT OUTER from master to each of your child tables, and you should be rocking and rolling in no time!
BTW, thanks for being up-front about this being an assignment. It is refreshing to see a student with a smidgeon of integrity, since there are SO many students that try to pass their homework off as work!
Hi, I´ve tested LEFT OUTER in this way (to verify
that the correct “sums” are displayed):
select invoices.inumber,invoices.amount,sum(payments.amou nt),
left outer join payments on invoices.inumber=payments.inumber
left outer join creditnotes on invoices.inumber=creditnotes.inumber
where invoices.status='not paid'
group by invoices.inumber;
But there's a weird situation, an example results displayed are:
but the real results must be:
inumber sum(payments.amount) sum(creditnotes.amount)
1 10 3
2 10 4
In invoice 1 there's only one payment of $10 and two credit notes
that sum $3, i think mysql is showing results x 2 because there are two records of credit notes.
The same situation happens in invoice 2: there are three payments
that sum $10 and one credit note that sum $4, and because there
are three records of payments the results displayed are x 3.
How can I avoid this situation? I think I´m really close to the end,
and I will be rocking and rolling!
Ouch! This is one place where the limitations of MySQL hurt me. The lack of a correlated sub-query is a killer for this kind of processing using only standard SQL. There may be some MySQL specific solution that will allow you to solve this using a simple select, but I don't know how to do it.
The best answer I can offer is to use a temporary table. I think that you'll need to:
1) populate the temp table using the amount and the sum of the payments for all of the invoice numbers that have a status of 'not paid'
2) LEFT OUTER JOIN that temporary table to the credits to produce the final result.
Uff-da! I need to think on this one a bit. It seems like a nasty shortcoming to me.