On Thursday 07 July 2005 02:19 pm, Marvin Bellamy wrote: > Can anyone explain why these queries don't work? And, is there a > workaround? > > create table t1 (id int); > create table t2 (id int, name varchar(32)); > create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; ugly way of doing a join query but we'll go with it ;)
better to use this instead: create view v2 as select a.id, b.name from t1 a inner join t2 b on a.id=b.id; > > select * from v1 where a.id=1; bad column name > select * from v1 where t1.id=1; same here > select * from v1 where t2.id=1; and here > select * from v1 where id=1; this one works for me. $ sqlite SQLite version 2.8.16 Enter ".help" for instructions sqlite> create table t1(id int); sqlite> create table t2 (id int, name varchar(32)); sqlite> insert into t1 values(1); sqlite> insert into t1 values(2); sqlite> insert into t1 values(3); sqlite> insert into t2 values(1, 'blah'); sqlite> insert into t2 values(2, 'blah2'); sqlite> create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; sqlite> select * from v1; 1|blah 2|blah2 sqlite> .headers on sqlite> select * from v1; id|name 1|blah 2|blah2 sqlite> select * from v1 where id=1; id|name 1|blah sqlite> select * from v1 where a.id=1; SQL error: no such column: a.id sqlite> select * from v1 where t1.id=1; SQL error: no such column: t1.id sqlite> select * from v1 where t2.id=1; SQL error: no such column: t2.id
