Re: [sqlite] Multiples natural left joins problem

2009-12-06 Thread Simon Davies
2009/12/6 Yuzem : > > Is this a bug in new versions that will be fixed or it is the new behavior? In 3.6.20, so long as you include the alias, you will get your expected results: SELECT movies.id, title, files, icon_modified, icon_width FROM ( movies NATURAL LEFT JOIN files) AS movies NATURAL LEFT

Re: [sqlite] Multiples natural left joins problem

2009-12-06 Thread Yuzem
Is this a bug in new versions that will be fixed or it is the new behavior? -- View this message in context: http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p2730.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Multiples natural left joins problem

2009-12-04 Thread Yuzem
> You have not specified your version, but I can repeat your > difficulties when using version 3.6.19 and 3.6.20 > I am using version 3.6.16 > You can get 'correct' results if you modify your query to > SELECT movies.id, title, files, icon_modified, icon_width > FROM ( movies natural left j

Re: [sqlite] Multiples natural left joins problem

2009-12-04 Thread Simon Davies
2009/12/4 Yuzem : > >> 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

Re: [sqlite] Multiples natural left joins problem

2009-12-04 Thread Yuzem
> 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 que

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Simon Davies
2009/12/3 Yuzem : > > . . . > Mine is a little more complex actually: > CREATE TABLE movies(id INTEGER,year INTEGER,rating INTEGER,votes > INTEGER,runtime INTEGER,iconWidth INTEGER,iconHeight INTEGER,iconAlpha > INTEGER,iconModified INTEGER,title TEXT,type TEXT,plot TEXT,color > TEXT,unique(id)) >

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Yuzem
SimonDavies wrote: > > sqlite> create table movies( id integer primary key, title text ); > sqlite> insert into movies( title ) values( 'movie1' ); > sqlite> insert into movies( title ) values( 'movie2' ); > sqlite> insert into movies( title ) values( 'movie3' ); > sqlite> > sqlite> create tabl

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Simon Davies
2009/12/3 Simon Davies : > 2009/12/3 Yuzem : >>> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >>> user) as a natural left join tag; >>> >> It doesn't work, it says: no such column a.id >> If I use movies.id I have the same problem as before: movies with no rating >> don't get

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Simon Slavin
On 3 Dec 2009, at 5:30pm, Yuzem wrote: > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: movies with no ra

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Pavel Ivanov
>> select movies.id, title, rating, tag >> from movies left join user on movies.id = user.id >> left join tag on movies.id = tag.id; >> > Ok, this works and it is fast but isn't any way to make this a little > simpler because the real query involves more tables and more columns and it > be

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Simon Davies
2009/12/3 Yuzem : > > Thanks both for the replies. . . . . > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: m

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Yuzem
Thanks both for the replies. Pavel Ivanov-2 wrote: > > If using "natural left join" is not a requirement for you then this > works as you expect it: > select movies.id, title, rating, tag > from movies left join user on movies.id = user.id > left join tag on movies.id = tag.id; > Ok, t

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Simon Davies
2009/12/3 Pavel Ivanov : > If using "natural left join" is not a requirement for you then this > works as you expect it: > > select movies.id, title, rating, tag > from movies left join user on movies.id = user.id >        left join tag on movies.id = tag.id; > Or: SELECT a.id,a.title,a.rating,ta

Re: [sqlite] Multiples natural left joins problem

2009-12-03 Thread Pavel Ivanov
If using "natural left join" is not a requirement for you then this works as you expect it: select movies.id, title, rating, tag from movies left join user on movies.id = user.id left join tag on movies.id = tag.id; Pavel On Thu, Dec 3, 2009 at 9:49 AM, Yuzem wrote: > > Lets say I have

[sqlite] Multiples natural left joins problem

2009-12-03 Thread Yuzem
Lets say I have the following tables: movies: id,title user: id,rating tag: id,tag I want to left join all three tables: SELECT movies.id,movies.title,user.rating,tag.tag FROM movies natural left join user natural left join tag The problem with this besides having to specify the tables