In-Reply-To: <466FEEC9.4050300@lake-lotawana.mo.us>
References: <466FEEC9.4050300@lake-lotawana.mo.us>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <D6358648-EFC8-489A-8F07-450CA5B85795@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: join problem
Date: Wed, 13 Jun 2007 08:58:30 -0500
To: A. R. Van Hook <hook@lake-lotawana.mo.us>


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