From: Michael Glaesemann <grzm@seespotcode.net>
To: A. R. Van Hook <hook@lake-lotawana.mo.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: join problem
Date: Wed, 13 Jun 2007 08:58:30 -0500
Message-ID: <D6358648-EFC8-489A-8F07-450CA5B85795@seespotcode.net> (raw)
In-Reply-To: <466FEEC9.4050300@lake-lotawana.mo.us>
References: <466FEEC9.4050300@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, hook@lake-lotawana.mo.us
Subject: Re: join problem
In-Reply-To: <D6358648-EFC8-489A-8F07-450CA5B85795@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox