Re: Query, view join question.

"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