Thread: Query, view join question.
Hi all, I have 2 tables: table1 with the columns objectid, refobjectid, commonvaluecol and value1. table2 with the columns objectid, refobjectid, commonvaluecol and value2. A "select * from table2 where commonvaluecol = 123 and value2 > 0" returns no rows. I create a view: create view miracle as select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 from table1 joing table2 on table1.refobjectid = table2.refobjectid where commonvaluecol = 123 Than I do a "select * from miracle where commonvaluecol = 123 and value2 > 0" This query returns many rows. (How) Is this possible? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> Hi all,
> I have 2 tables:
> table1 with the columns objectid, refobjectid, commonvaluecol and value1.
> table2 with the columns objectid, refobjectid, commonvaluecol and value2.
> A "select * from table2 where commonvaluecol = 123 and value2 > 0" returns no rows.
> I create a view:
> create view miracle as
> select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2
> from table1 joing table2 on table1.refobjectid = table2.refobjectid
> where commonvaluecol = 123
regression=# create table table1(objectid int, refobjectid int, commonvaluecol int, value1 int);
CREATE TABLE
regression=# create table table2(objectid int, refobjectid int, commonvaluecol int, value2 int);
CREATE TABLE
regression=# create view miracle as
regression-# select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2
regression-# from table1 joing table2 on table1.refobjectid = table2.refobjectid
regression-# where commonvaluecol = 123;
ERROR: syntax error at or near "table2" at character 135
LINE 3: from table1 joing table2 on table1.refobjectid = table2.refo...
^
regression=# create view miracle as
regression-# select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2
regression-# from table1 join table2 on table1.refobjectid = table2.refobjectid
regression-# where commonvaluecol = 123;
ERROR: column table1.commonvalue does not exist
regression=# create view miracle as
regression-# select table1.objectid, table1.value1, table1.commonvaluecol, table1.refobjectid, table2.value2
regression-# from table1 join table2 on table1.refobjectid = table2.refobjectid
regression-# where commonvaluecol = 123;
ERROR: column reference "commonvaluecol" is ambiguous
regression=#
Please don't waste our time with erroneous examples.
regards, tom lane
You haven't given the complete information but the following is just a guess. It seems that abonnement is in both tables. The view is stating abo_his.abonnement = 238 I assume that your initial query was WHERE abo_klt.abonnement = 238 and AND abo_klt.afgewerkt > 2 My guess is that you are asking the view a different question: WHERE abo_his.abnnement = 238 AND abo_klt.afgewerkt > 2 > Hi all, > > I have 2 tables: > > table1 with the columns objectid, refobjectid, commonvaluecol and value1. > table2 with the columns objectid, refobjectid, commonvaluecol and value2. > > A "select * from table2 where commonvaluecol = 123 and value2 > 0" > returns no rows. > > I create a view: > > create view miracle as > select table1.objectid, table1.value1, table1.commonvalue, > table1.refobjectid, table2.value2 > from table1 joing table2 on table1.refobjectid = table2.refobjectid > where commonvaluecol = 123 > > Than I do a "select * from miracle where commonvaluecol = 123 and value2 > > 0" > > This query returns many rows. (How) Is this possible? > > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > e-mail: J.Kraaijeveld@Askesis.nl > web: www.askesis.nl > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >