2009/12/3 Yuzem <[email protected]>: > > . . . > 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)) > > CREATE TABLE files(id INTEGER,modified INTEGER,size INTEGER,files > TEXT,unique(files)) > > CREATE TABLE icons_movies(icon_width integer,icon_height integer,icon_alpha > integer,icon_modified integer,id text,unique(id)) > > Movies with no files will not display the icons. Don't know why. Maybe > because id is text in icons and integer in the others? But it joins, just, > not as expected... >
So I change: sqlite> drop table tag; sqlite> create table tag( id text, tag text ); sqlite> insert into tag( id, tag ) values( '1', 'tag1' ); sqlite> insert into tag( id, tag ) values( '2', 'tag2' ); sqlite> insert into tag( id, tag ) values( '3', 'tag3' ); sqlite> insert into tag( id, tag ) values( '4', 'tag4' ); sqlite> sqlite> SELECT id,title,rating,tag FROM (movies natural left join user) natural left join tag; id|title|rating|tag 1|movie1|rating1|tag1 2|movie2|rating2|tag2 3|movie3||tag3 text v integer appears to make no difference What query are you trying with the above tables, and how do the results differ from what you expect? > > SimonDavies wrote: Simon Slavin actually... >> >> You need to read up on the different types of JOIN that SQL allows. No >> reason to use a LEFT JOIN if you want a RIGHT JOIN, an INNER JOIN or a >> CROSS JOIN. >> > It says that right join is not supported and inner and cross join don't give > the expected result, they duplicate movies if there are more than one tag > for every movie. Regards, Simon (Davies) _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
