Обсуждение: join problem
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" Where do I add the cai table reference??? thanks -- Arthur R. Van Hook Mayor - RetiredThe City of Lake Lotawana hook@lake-lotawana.mo.us hook@lota.us avanhook3@comcast.net (816) 578-4704 - Home (816) 564-0769 - Cell
A. R. Van Hook a écrit : > 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" > > Where do I add the cai table reference??? > You probably need to change the order of the tables in the FROM clause. Replace FROM invoice v, cai with FROM cai, invoice v Regards. -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://docs.postgresqlfr.org/ -->
You can do it like this: 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 join caion v.cusid = cai.cusid left outer join invoiceitems i on v.ivid = i.ivid where v.cusid = $cusid group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db A. Aleš Vojáček FBL Group spol. s r.o. e-mail: alesv@fbl.cz mobil: +420603893335 A. R. Van Hook napsal(a): > 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" > > Where do I add the cai table reference??? > > thanks >
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
There is not referenced table cai in from clausule. A. R. Van Hook napsal(a): > 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" > > Where do I add the cai table reference??? > > thanks >
[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