2009/12/4 Yuzem <naujnit...@gmail.com>: > >> What query are you trying with the above tables, and how do the >> results differ from what you expect? >> > This is the query: > SELECT movies.id,title,files,icon_modified,icon_width > FROM (movies natural left join files) natural left join icons_movies > LIMIT 25 ; > > The difference with your last query is that I have to specify the tables or > it says "ambiguous column name" and the problem with the result is that if a > movie doesn't have an entry in the table "files" it doesn't show the icon > properties.
Note that I have been using version 3.4.2, in which your query works fine. You have not specified your version, but I can repeat your difficulties when using version 3.6.19 and 3.6.20 You can get 'correct' results if you modify your query to SELECT movies.id, title, files, icon_modified, icon_width FROM ( movies natural left join files) as movies natural left join icons_movies LIMIT 25; Can anyone explain this behavioural difference? Regards, Simon SQLite version 3.6.20 Enter ".help" for instructions sqlite> sqlite> create table t1( id integer primary key, t1data text ); sqlite> insert into t1( t1data ) values( 't1data1' ); sqlite> insert into t1( t1data ) values( 't1data2' ); sqlite> sqlite> create table t2( id integer primary key, t2data text ); sqlite> insert into t2( t2data ) values( 't2data1' ); sqlite> sqlite> create table t3( id integer primary key, t3data text ); sqlite> insert into t3( t3data ) values( 't3data1' ); sqlite> insert into t3( t3data ) values( 't3data2' ); sqlite> sqlite> SELECT t1.id, t1data, t2data, t3data FROM (t1 natural left join t2) as t1 natural left join t3; 1|t1data1|t2data1|t3data1 2|t1data2||t3data2 sqlite> SELECT t1.id, t1data, t2data, t3data FROM (t1 natural left join t2) natural left join t3; 1|t1data1|t2data1|t3data1 2|t1data2|| sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
