Hi,

I was wondering if the following is acceptable SQL:

Select Customer, Count(Invoices) Invoices, Sum(Amount) Amount
From Customers C
left outer join Invoices I on (I.CustID = C.ID) and (not I.Paid)
Group by Customer


What I'm attempting to achieve is a list of every customer, but to show
the number of invoices and amounts of invoices that are outstanding.

If I move the "Not I.Paid" to a where clause instead, it will only show
me a list of customer that have outstanding invoices. I want to see all
customers instead, and those without invoices. I'm hoping by using it in
the left outer join that it will only join records that  are unpaid, but
since it's a left outer join the statement won't affect the actual
customer list.

The other methods would be to use a union, or memory tables, but this
looks like the simplest method - I'm just not sure if it's acceptable
SQL or whether there are any traps?

Cheers

Adam.

Adam

>Select Customer, Count(Invoices) Invoices, Sum(Amount) Amount
> From Customers C
>left outer join Invoices I on (I.CustID = C.ID) and (not I.Paid)
>Group by Customer

This "'I'm hoping by" makes me think you haven't tried it, and , even without trying it (and I don't have anything suitable to try it on) something about it bothers me.

I think you'd be better off trying something along the lines of

Select Customer, Count(Invoices) Invoices, Sum(IF(not i.Paid,Amount,0)) Amount
From Customers C
left outer join Invoices I on (I.CustID = C.ID)
Group by Customer


Roy Lambert

Good Morning Roy,

Thanks for the reply. Unfortunately the example I've given is a generic
one (as people tend to understand invoices and customers).

In my real world example, I need to show the other table even if there's
no link at all. (So, in the example, I would still need a list of
invoices as well - even if there are no customers attached).

I have ended up trying the above and putting the condition in the left
outer join. It seems to work. I wasn't aware whether there could be any
complications however, as I've never put these types of conditions in
the left outer join and wasn't sure whether it was accepted, or whether
this was just a fluke and there are traps ahead.

If no one has a firm understanding either way, then I'll see how I go
with this, otherwise I might drop Tim a direct email to see his thoughts.

Cheers

Adam.

Adam


The join condition is essentially just a boolean test so I can cope with and understand that its the left/right inner/outer that I struggle with and generally end up experimenting. If it works use it.

However, I like the idea of invoices without customers. Just what I need for a bit of creative fraud. I assume there's a good reason for this. If nnot I recommend a separate report highlighting the invoices without customers.

Roy Lambert

Hi Roy,

> The join condition is essentially just a boolean test so I can cope with and understand that its the left/right inner/outer that I struggle with and generally end up experimenting. If it works use it.

Excellent - that's what I'm going with at this stage.

> However, I like the idea of invoices without customers. Just what I need for a bit of creative fraud. I assume there's a good reason for this. If nnot I recommend a separate report highlighting the invoices without customers.

LOL - it's called 'Creative Accounting Software', I'm expecting to make
a small fortune.


The real application has nothing to do with accounting. Since the stuff
I do is relatively unique I find it's normally easier to convert what
I'm trying to achieve to something common such as an accounting package
for everyone else to understand. (Otherwise it creates more questions
than I get answers )

Cheers

Adam.