Re: join problem

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