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 table user( id integer primary key, rating text );
> sqlite> insert into user( rating ) values( 'rating1' );
> sqlite> insert into user( rating ) values( 'rating2' );
> sqlite>
> sqlite> create table tag( id integer primary key, tag text );
> sqlite> insert into tag( tag ) values( 'tag1' );
> sqlite> insert into tag( tag ) values( 'tag2' );
> sqlite> insert into tag( tag ) values( 'tag3' );
> sqlite>
> sqlite> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left
> join user
> ) as a natural left join tag;
> 1|movie1|rating1|tag1
> 2|movie2|rating2|tag2
> 3|movie3||tag3
> sqlite>
>
> Regards,
> Simon
>
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...
SimonDavies wrote:
>
> 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.
--
View this message in context:
http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26630530.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
