[rolandpish]

Hi, I´m not so experienced in SQL, I´m stuck
with something I need for a homework so I have
this situation:

I have a master table and two child tables
as follows:

Master table: Invoices (pk= inumber)
Child table #1: Client Payments (fk= inumber)
Child table #2: Client credit notes (fk= inumber)


I need to get how much is left for each invoice,
so I know how much each client owes me.

The pseudo formula is something like this:

Invoice amount - (Payments total sum + Credit notes total sum)

So the SQL I made for now only gets correctly this part
of the formula:

Invoice total - Payments total

The SQL is as follows:

select invoices.inumber,invoices.amount-sum(payments.amount)
from invoices
left join payments on invoices.inumber=payments.inumber
where invoices.status='not paid'
group by invoices.inumber;

(by the way: I´m using MySQL)

But now I´m stuck for 4 days modifying the SQL and now I´m
desperate and my brain is going to explode.

Thanx very very very much in advance.

[Pat Phelan]

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!

-PatP

[rolandpish]

Thanks a lot Pat,
i´ll test it tonight,
and thanks for your words!!

Roland

[rolandpish]

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),
sum(creditnotes.amount)
from invoices
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:

inumber sum(payments.amount) sum(creditnotes.amount)
1 20 6
2 30 12
...

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!

Thanks in advance

Roland

[Pat Phelan]

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.

-PatP