Re: [sqlite] inner join problem

[Keith Medcalf]

> update dzhhq set mnote=
> case when  mnote in (select mnote from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
> else mnote
> end
 
> is ok, but when I use this cmd then appear error msg:
 
> update dzhhq set mnote=
> case when mnote in (select mnote from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode and (dzhhq.qph60 and (buysell.bs="sell")) then 'sell'||mnote
> else mnote
> end

Buysell.qph60, buysell.bs, and "sell" are not visible columns.  You don't 
include the actual error message. " and ' quote different things.  ' quotes 
string values and " quotes names (database/table/column/index ).

update dzhhq set mnote=
case when mnote in (select mnote from dzhhq, buysell where 
dzhhq.stkcode=buysell.stkcode and dzhhq.qph60http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] inner join problem

[YAN HONG YE]

update dzhhq set mnote=
case when  mnote in (select mnote from dzhhq inner join (select bs,stkcode from 
buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
else mnote
end

is ok, but when I use this cmd then appear error msg:

update dzhhq set mnote=
case when mnote in (select mnote from dzhhq inner join (select bs,stkcode from 
buysell) b on dzhhq.stkcode=b.stkcode and (dzhhq.qph60

Re: [sqlite] Inner join problem

[Simon Berthiaume]

I once posted about that on this mailing list and one of the solution Mr
DRH suggested was to auto-expand the table name ( would
become SELECT * FROM ) internaly in the SQLite API, but I
have no idea if any efforts were put in that direction since it could
probably break the old syntax.

Ce fut un plaisir de pouvoir aider un confrère.


Simon B.

 
On Tue, 2004-03-09 at 09:00, Guillermo Fernandez Castellanos wrote:

> 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]
> 

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]

Re: [sqlite] Inner join problem

[Simon Berthiaume]

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]
> 

[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]