Re: [sqlite] Join query help

[Mitchell Vincent]

Really appreciate your help! However that query doesn't give correct 
results (though it does give a row for every customer!!!)..

The problem is the sum() in the join isn't qualified against the 
selected customer ID..

Using this :
SELECT c.customer_number as customer_number coalesce(ctots.balance_due, 
0.00) as balance FROM customers as c left join (Select customer_id cid, 
sum(balance_due) balance_due FROM invoice_master group by cid) ctots on 
c.customer_id = ctots.cid ORDER by c.customer_name ASC

I need the sum() to be for the current customer ID coming from the other 
query..

So this :
(Select customer_id cid, sum(balance_due) balance_due FROM 
invoice_master group by cid)

Needs to become something like :
(Select customer_id cid, sum(balance_due) balance_due FROM 
invoice_master WHERE customer_id = c.customer_id group by cid)

But that doesn't work as I get a "no such column c.customer_id"
Kurt Welgehausen wrote:
select customers.*, ctots.total
from customers,
     (select customer_id cid, sum(invoice_amount) total
      from invoice_master group by cid) ctots
where customers.customer_id = ctots.cid

You're right -- sorry, I wan't paying attention.
For 'customers, (subquery) where' substitute
'customers left join (subquery) on', and
for 'ctots.total' subsitute 'coalesce(ctots.total, 0.00)'.
select customers.*, coalesce(ctots.total, 0.00)
from customers left join
     (select customer_id cid, sum(invoice_amount) total
      from invoice_master group by cid) ctots
on customers.customer_id = ctots.cid
Regards
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]