Hi all,
I have spent hours working on this query...probably an easy one for most of
you...
I need to sum records in two tables using a third table as the driver or
base table (I think that is the correct name)
Logic:
The customers table will ONLY return one row and one field (cust_id) based
on c.cust_id = 1926
The ar_ledger table will return 0 or more rows and the field "amount" must
be summed for all the rows it returns.
The ar_ledger table has a field "cust_id" which must match the one row that
the customers table returns
The rec_inv table will return 0 or more rows and the field "amount" must be
summed for all the rows it returns.
The rec_inv table has a field "cust_id" which must match the one row that
the customers table returns
I try
select sum(a.amount) as balance, sum(r.amount) as monthly
from ar_ledger a, rec_inv r, lists.customers c
where
r.cust_id = c.cust_id and
a.cust_id = c.cust_id and
c.cust_id = 1926
group by c.cust_id
and I get (it appears) the correct balance and a monthly * about 100
I can't see how it is returning too many rows for the rec_inv. Shouldn't
rec_inv only have back all rows which match cust_id? and the same for
ar_ledger?
Is it my group by clause?
Any assistance would be appreciated...
-Dave
