"Joost Kraaijeveld" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster