Quoth Igor Tandetnik <itandet...@mvps.org>, on 2010-09-24 08:16:07 -0400: > sqlite> create table A (id integer primary key); > sqlite> create table B (id integer primary key, name text); > sqlite> select * from A as X order by (select name from B where B.id = A.id); > Error: no such column: A.id > sqlite> select * from A as X order by (select name from B where B.id = X.id); > sqlite> > > One would expect that table name and alias should work > interchangeably except when the name is ambiguous (which is not the > case here).
I wouldn't, actually. I would expect the table alias to "overwrite" the original table name as the source name; essentially a table named "t" when used in a SELECT statement generates a source named "t" by default, and "t AS s" or "t s" generates a source named "s" instead. Tables are not themselves sources in the strictest sense; a source references a table (and "is" the table for many purposes). http://archives.postgresql.org/pgsql-general/2000-08/msg00740.php claims that SQL92 requires this behavior. ---> Drake Wilson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
