Re: join problem - Mailing list pgsql-sql

[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