[sqlite] Inner join problem

[Guillermo Fernandez Castellanos]

Hi,

I've had problems with inner join transactions.

Here is what I do:

I create the tables:
sqlite> create table main (
   ...> id integer primary key,
   ...> primid int(10),
   ...> secid int(10)
   ...> );
sqlite>
sqlite> create table prim (
   ...> primid integer primary key,
   ...> primname varchar(10)
   ...> );
sqlite>
sqlite> create table sec (
   ...> secid integer primary key,
   ...> secname varchar(10)
   ...> );
I populate the tables:
sqlite> insert into prim(primid,primname) values (null,'prim1');
sqlite> insert into prim(primid,primname) values (null,'prim2');
sqlite> insert into prim(primid,primname) values (null,'prim3');
sqlite>
sqlite> insert into sec(secid,secname) values (null,'sec1');
sqlite> insert into sec(secid,secname) values (null,'sec2');
sqlite> insert into sec(secid,secname) values (null,'sec3');
sqlite>
sqlite> insert into main(id,primid,secid) values (null,1,1);
sqlite> insert into main(id,primid,secid) values (null,2,2);
sqlite> insert into main(id,primid,secid) values (null,3,3);
I check the integrity of my tables:
sqlite> select * from main;
1|1|1
2|2|2
3|3|3
sqlite> select * from prim;
1|prim1
2|prim2
3|prim3
sqlite> select * from sec;
1|sec1
2|sec2
3|sec3
I do a simple inner join to check that it is supported by sqlite:
sqlite> select main.id,prim.primname from main inner join prim on
main.primid=prim.primid;
1|prim1
2|prim2
3|prim3
I then try a more complicated inner join:
sqlite> select main.id,prim.primname,sec.secname from (main inner join sec on
main.secid=sec.secid) inner join prim on main.primid=prim.primid;
SQL error: near "main": syntax error
sqlite>
sqlite> select main.id,prim.primname,sec.secname from sec inner join (main 
inner join prim on prim.primid=main.primid) on sec.secid=main.secid;
SQL error: near "main": syntax error
sqlite>

Why does it not work?

Can this be considered as a nested transaction?
If it's the case, how could I do something similar?
Thanks a lot,

G.

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]