Re: join problem

[Please don't top post as it makes the discussion more difficult to
follow, and please reply to the list so that others may benefit from
and participate in the discussion.]

On Jun 19, 2007, at 14:17 , A. R. Van Hook wrote:

> Michael Glaesemann wrote:
>>
>> On Jun 13, 2007, at 8:19 , A. R. Van Hook wrote:
>>
>>> I have join problem:
>>> "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db,
>>> sum(i.tax) as tax,
>>> sum(i.tax + i.rowtot) as totalP,
>>> (sum(i.tax + i.rowtot) - v.deposit) as balance
>>> from invoice v
>>> left outer join
>>> invoiceitems i
>>> on v.ivid = i.ivid
>>> where v.cusid = $cusid
>>> and v.cusid = cai.cusid
>>> group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db
>>> ERROR: missing FROM-clause entry for table "cai"
>>>
>>> If I add cai to the from clause "from invoice v, cai, I get
>>> ERROR: missing FROM-clause entry for table "cai"
>>> ERROR: invalid reference to FROM-clause entry for table "v"
>>
>> I think you may need to change the order of the JOIN clause. Does
>> this work?
>>
>> SELECT i.ivid
>> , v.eventdate
>> , v.deposit
>> , v.invdate
>> , cai.db
>> , sum(i.tax) as tax
>> , sum(i.tax + i.rowtot) as totalP
>> , (sum(i.tax + i.rowtot) - v.deposit) as balance
>> FROM cai
>> JOIN invoice v ON (cai.cusid = v.cusid)
>> LEFT JOIN invoiceitems i ON (v.ivid = i.ivid)
>> WHERE v.cusid = $cusid
>> GROUP BY i.ivid
>> , v.eventdate
>> , v.deposit
>> , v.invdate
>> , cai.db
>>
>> Note I've also moved the cai.cusid = v.cusid into the JOIN
>> condition (which is what it is). Also, if cai doesn't have a ivid
>> column and invoiceitems doesn't have a cusid column, you can use
>> USING (cusid) and USING (ivid) rather than ON (cai.cusid =
>> v.cusid) and ON (v.ivid = i.ivid), which has the nice property of
>> outputing only one join column rather than one column for each
>> table, (i.e., only one cusid column rather than one each for cai
>> and invoice).
>>
>> Michael Glaesemann
>> grzm seespotcode net

> This solution works fine but the summations are reporting
> individual row data.
> i.e.
> ivid | eventdate | deposit | invdate | db | tax | totalp
> | balance
> ------+------------+---------+------------+------+--------+---------
> +---------
> 7610 | 10/15/2005 | 0.00 | 05/05/2005 | 0.00 | 11.490 | 170.490
> | 170.490
> 7868 | 10/15/2005 | 85.25 | 06/04/2005 | 0.00 | |
> | 8620 | 10/15/2005 | 85.24 | 09/07/2005 | 0.00 | 0.000
> | 0.000 | -85.240
>
> can the query be modified to get the overall totals of each
> (db,tax,totalp,balance)?

If you want totals for db, tax, totalp, and balance, you'll need to
modify the rows that are returned (the SELECT list) and the GROUP BY
clause to group those together. I don't know what you want to the
totals over: eventdate? ivid? Give it a try and if you still have
questions, be sure to post what you've attempted.

Michael Glaesemann
grzm seespotcode net