I think I know what the problem is. SQLite doesn't like having a table
name following an openint parenthesis "(" in the FROM clause. Try the
following statements instead:
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;
Or
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;
Simon B.
On Tue, 2004-03-09 at 07:52, Guillermo Fernandez Castellanos wrote:
> 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]
>
