Re: [sqlite] Inner join problem

[Guillermo Fernandez Castellanos]

Sweet... works right perfect.

Can it be considered as a bug?

Merci beaucoup!

Guille

Simon Berthiaume wrote:
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]




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