Re: [sqlite] 3 tables in relation, really slow ...
Have you tried to change the order of the conditions in the WHERE clause? I don't know the internal working of SQLite so they might actualy give worst results. You can try something like that: SELECT date_ext.mydate as MyDate, city_ext.city as MyCity, number_ext.mynum as MyNumber FROM city_ext, number_ext, date_ext WHERE date_ext.mydate = ( SELECT MAX( date_ext.mydate ) FROM date_ext ) AND number_ext.mydate = date_ext.id AND number_ext.city = city_ext.id ORDER BY city_ext.city, number_ext.position; Or something a bit more weird like SELECT date_maxed.mydate as MyDate, city_ext.city as MyCity, number_ext.mynum as MyNumber FROM city_ext, number_ext, (SELECT * FROM date_ext WHERE date_ext.mydate = ( SELECT MAX( date_ext.mydate ) FROM date_ext )) AS date_maxed WHERE number_ext.mydate = date_maxed.id AND number_ext.city = city_ext.id ORDER BY city_ext.city, number_ext.position; Tell me if you get any wost or better results. On Tue, 2004-09-07 at 01:59, Andrea Giammarchi wrote: > Hi All, > I'm doing some tests with SQLite ( 2.8.14 ) against MySQL ( 4.1.3 beta ) > with these 2 tables: > > date_ext ( id INTEGER PRIMARY KEY, mydate DATE not null ) > city_ext ( id INTEGER PRIMARY KEY, city CHAR( 2 ) ) > number_ext ( id INTEGER PRIMARY KEY, mydate INTEGER not null, city > INTEGER(2) not null, position INTEGER(1) not null, mynum INTEGER(2) not > null ) > > > ... and this is the query: > > SELECT date_ext.mydate as MyDate, city_ext.city as MyCity, number_ext.mynum > as MyNumber FROM city_ext, number_ext, date_ext WHERE number_ext.mydate = > date_ext.id AND number_ext.city = city_ext.id AND date_ext.mydate = ( SELECT > MAX( date_ext.mydate ) FROM date_ext ) ORDER BY city_ext.city, > number_ext.position > > > This query is done in MySQL in 0.31416 seconds > > In SQLite is done in 1.408461 seconds ( with the same result of MySQL > query ) > > Does anyone know why there's too much difference from this simple query in > the same type of tables ??? > > DATE is a column with '-MM-DD' info, date_ext contains 3406 records, > city_ext contains 10 records, number_ext contains 170300 records. > > Platform is Windows 2000 with Apache 1.3.31 and PHP5.0.1 as CGI with > dedicated port :81 > > Thank you. > andr3a > > > >
[sqlite] Query maximum JOIN
Hi, I would like to know if there is any maximum to the number of JOIN (LEFT JOIN actualy) I can put in a query. I tried to make a query with 31-32 JOIN and the engine returned no row, but returned no error message either so I don't know. Just a single JOIN before, the query was still returning data, but not anymore. Thank you for your time. Simon Berthiaume
Re: [sqlite] A proposal for SQLite version 3.0
It might be true if you mostly store large quantities of NUMERICAL data (float, int, ...) since they are currently stored as strings (the value 1234567, would be stored using at least 7-8 bytes instead of 4). If you were in the same situation as I, where I use database mostly for strings, you would be in the opposite situation, the SQLite database would be about half the size of the MS Access one, since MS Access seems to save all strings as UTF-16. Simon B. On Wed, 2004-04-07 at 10:50, Jakub Adamek wrote: > Hello, > > I am using SQLite in a car navigation system which should work on PDAs > as well. Thus speed and size is crucial for us. SQLite is superb in the > speed category, but the size of its file is not so superb. I see you > mentioned something about file size. My experience is that SQLite makes > roughly about 3x bigger files than MS Access. How would this change in 3.0? > > Thanks for your excellent work, > > Jakub Adamek > > D. Richard Hipp wrote: > > > A design proposal for SQLite version 3.0 can be found at: > > > > http://www.sqlite.org/prop2.html > > > > Feedback from the user community is strongly encouraged. > > An executive summary of the proposed changes follows: > > > >* Support for UTF-16 > >* Better BLOB support > >* User-defined collating sequences (for better > > internationalization support) > >* Smaller and faster than 2.8.13. > > > > The plan is to continue to support the 2.8.X series > > indefinately and in parallel to the 3.X series. But > > the only changes to 2.8.X going forward will be bug > > fixes. New features will go into 3.X. Beta releases > > of version 3.X are expected within a few months. > > > > I do not have much experience with UTF-16 and am > > expecially interested in feedback on that area of > > the design. > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] >
Re: [sqlite] How to use last_insert_rowid()?
Here are 2 tables to use in our example: CREATE TABLE t1 ( t1_pk INTEGER PRIMARY KEY, t1_value VARCHAR(20) ); CREATE TABLE t2 ( t1_fk INTEGER, t2_id INTEGER, t2_value VARCHAR(20) ); t1_pk would be the primary key on your main table (t1), t1_fk would be foreign key to t1_pk and t1_fk+t2_id (not mathematical addition) would form a unique identifier for any t2 record. Since t1_pk is declared as an INTEGER PRIMARY KEY it will always hold the same value as ROW_ID. So once you have called sqlite_exec() with your insert statement for t1, call last_insert_rowid() and use that value as the value for t1_fk when you will do yhe insert in t2. Hope this can help you a bit, if still not sure about how to proceed, just mail me back. Simon B. On Tue, 2004-04-06 at 00:01, Rob Duncan wrote: > Greetings, > > I have what is, I think, a very basic SQL question, but I don't seem to > be able to find the answer. I'm almost a complete database novice, so > perhaps I have things completely wrong… I want to insert a row into > one table and have it automatically generate a primary key (the other > fields in the insert are not going to be unique, only the primary key > is sure to be unique). I then want to add some rows to other tables > that refer to the first row with a foreign key. So my question is, how > do I obtain the value of the first row's primary key, and how do I use > it as a foreign key in the subsequent inserts? > > The last_insert_rowid() function seems to be close to what I want. The > trouble with it is that as soon as I add a row to the first child table > its value will change and I will not be able to use it for any other > child inserts. > > Thanks, > > Rob. > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] >
[sqlite] Feature of "FROM" clause
Hi, given the following tables: CREATE TABLE table_1(id INTEGER PRIMARY KEY,column VARCHAR(64)); CREATE TABLE table_2(id INTEGER PRIMARY KEY,column VARCHAR(64)); I realized that the following query isn't valid in SQLite: SELECT * FROM ( table_1 INNER JOIN ( SELECT * FROM table_2 WHERE column = 'whatever' ) AS inner_1 ON table_1.id = inner_1.id ); It returns error message: SQL error: near "table_1": syntax error But if I write it like that it works just fine: SELECT * FROM table_1 INNER JOIN ( SELECT * FROM table_2 WHERE column = 'whatever' ) AS inner_1 ON table_1.id = inner_1.id ; It also works fine if I write the query like that: SELECT * FROM ( select * from table_1 INNER JOIN ( SELECT * FROM table_2 WHERE column = 'whatever' ) AS inner_1 ON table_1.id = inner_1.id ); To me the first query should be valid. it seems the SQL engine desn't like having a table name right after an opening parenthesis. Are there any standards regarding parenthesis in SQL92? Thank you Simon B.
Re: [sqlite] Inner join problem
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
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]
>
Re: [sqlite] ORDER BY failure (?)
What behavior were you trying to achieve? Putting a conditional statement (test1 > 100) in a GROUP BY clause isn't valid SQL. Simon B. On Tue, 2004-02-10 at 17:54, Andox wrote: > Hello everybody. > > I have a question about the SELECT command with UNION ALL. > > When executing the following command in sqlite.exe (win32, console) i get the > error message: > "SQL error: ORDER BY term number 1 does not match any result column" > > Command: > SELECT test1 FROM table1 UNION ALL SELECT test1 FROM table2 GROUP BY test1 > 100; > > So what i want to do, is to use ORDER BY for the total UNION result. > Is this some a typo by me or is there something "wrong" with sqlite? > > PS. I am a SQL newbie and sorry for my poor English :)
Re: [sqlite] qualified join
I had a similar problem recently, the way I solved it looks like that (it might not be pretty, but it works) SELECT t1.name, t2.deleted FROM t2 INNER JOIN ( SELECT t2.id, MAX(t2.time) AS last_time FROM t2 GROUP BY t2.id ) AS t3 ON t2.id = t3.id AND t2.time = t3.last_time INNER JOIN t1 ON t1.id = t2.id; Since SQLite support only STATIC inner queries, it's the only way I found to do it in a single query. Tell me if it worked for you. Simon B. On Fri, 2004-01-23 at 15:07, Michael Hunley wrote: > I have the following two table defs: > CREATE TABLE t1(id INTEGER PRIMARY KEY,name VARCHAR(64)); > CREATE TABLE t2(id INTEGER,time INTEGER,deleted INTEGER(1) PRIMARY > KEY(id,time)); > > My question is, how can I: "select t1.name,t2.deleted from t1 join t2 using > id" but only keep each row where t2.time is the max for that id? > > To put that another way, is there a single select I can issue which does > the equivalent of doing: > > CREATE TEMPORARY TABLE temp(id INTEGER,time INTEGER); > INSERT INTO temp (id,time) SELECT id,MAX(time),deleted FROM t2 GROUP BY id; > SELECT t1.name,t2.deleted FROM temp JOIN t1 USING id JOIN t2 USING id WHERE > t2.time=temp.time; > > I am hoping for some syntax that is the valid SQL/SQLite equivalent of: > SELECT t1.name,t2.deleted FROM t1 join t2 USING id WHERE t2.time=MAX(t2.time) > > thanks for any help. > > Michael Hunley > Senior Engineer > PocketPurchase, Inc. > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] >
Re: [sqlite] Query problem
For those of you that tends to write complex queries, I noted that SQLite doesn't like when a table name follows a opening parenthesis in the FROM clause. For example, the following works under Access ans Oracle, but not in SQLite: SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION FROM ( OBJECTS AS T0 LEFT JOIN ( SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) A LATEST_DATE FROM OBJECTS_LOCATION GROUP BY OBJECTS_LOCATION_FOREIGN ) AS T9 ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID ) LEFT JOIN OBJECTS_LOCATION AS T8 ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE) WHERE T0.OBJECTS_FLAG = 3 AND T0.OBJECTS_DELETED = 0 ORDER BY 2 ASC; To make it work, I have to write the following instead: SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION FROM OBJECTS AS T0 LEFT JOIN ( SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) AS LATEST_DATE FROM OBJECTS_LOCATION GROUP BY OBJECTS_LOCATION_FOREIGN ) AS T9 ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID LEFT JOIN OBJECTS_LOCATION AS T8 ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE) WHERE T0.OBJECTS_FLAG = 0 AND T0.OBJECTS_DELETED = 0 ORDER BY 2 ASC; I hope I will make someone save the trouble I went through finding that out. Simon Berthiaume On Mon, 2004-01-19 at 16:37, Simon Berthiaume wrote: > Maybe I used a too simplistic example. I have code that generates SQL > queries so users can request reports with various fields, various > criterias and various sorting orders. So technicaly there can be 1,2,3,N > inner queries with various statements in them. Here is an example of a > more complex query: > SELECT t0.OBJECTS_BRIEF, t2.OBJECTS_ALPHANUMERIC_VALUE, > t3.OBJECTS_DATE_VALUE > FROM > ( > ( > ( > SELECT * > FROM OBJECTS > WHERE OBJECTS_COLTYPE='Jewelry' > ) AS t0 > LEFT JOIN > ( > SELECT * > FROM OBJECTS_ALPHANUMERIC > WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='GRADE' > AND (OBJECTS_ALPHANUMERIC_VALUE='Excellent' > OR OBJECTS_ALPHANUMERIC_VALUE IS NULL) > ) AS t1 > ON t0.OBJECTS_ID = t1.OBJECTS_ID > ) > LEFT JOIN > ( > SELECT * > FROM OBJECTS_ALPHANUMERIC > WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='OWNER' > AND NOT OBJECTS_ALPHANUMERIC_VALUE='John Smith' > ) AS t2 > ON t0.OBJECTS_ID = t2.OBJECTS_ID > ) > LEFT JOIN > ( > SELECT * > FROM OBJECTS_DATE > WHERE OBJECTS_DATE_FIELD_ID='EXHIBIT_END_DATE' > ) AS t3 > ON t0.OBJECTS_ID = t3.OBJECTS_ID > ORDER BY 3,2; > > In the previous example, the user would have asked for > 1- the item's description, it's owner it's exhibit end date > 2- Objects that are Jewelry > 3- Objects in excellent condition (grade) or unspecified > 4- Objects not owned by John Smith > 5- Sorted by exhibit end date and owner name > > The "relationship" between OBJECTS and OBJECTS_ALPHANUMERIC is 0,N and > the "relationship" between OBJECTS and OBJECTS_DATE is also 0,N. > > The previous query worked just fine in Oracle and Access, but not in > SQLite. SQLite might simply not be support it, that is what I want to > know. If it's a bug, a feature SQLite will support in the future (how > close in the future) or a feature that will never be supported. > > Thank you all for your time > > > Simon Berthiaume > > > On Mon, 2004-01-19 at 14:24, Kurt Welgehausen wrote: > > > If you look at the SQLite grammar in lang.html, you'll see > > that parentheses are not allowed around a table-list. That's > > why you're getting an error. > > > > If you remove either of the first 2 left parens (and its > > corresponding right paren), the query will work, but the > > outer select and the first subselect serve no purpose. It's > > hard to predict performance just by reading a query, but this > > looks pretty inefficient. How about > > > > select * from > > INSCLAIMS t0 > > left join > > (select * from INSCLAIMS_CONCAT > > where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1 > > on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID; > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > >
Re: [sqlite] Query problem
Maybe I used a too simplistic example. I have code that generates SQL queries so users can request reports with various fields, various criterias and various sorting orders. So technicaly there can be 1,2,3,N inner queries with various statements in them. Here is an example of a more complex query: SELECT t0.OBJECTS_BRIEF, t2.OBJECTS_ALPHANUMERIC_VALUE, t3.OBJECTS_DATE_VALUE FROM ( ( ( SELECT * FROM OBJECTS WHERE OBJECTS_COLTYPE='Jewelry' ) AS t0 LEFT JOIN ( SELECT * FROM OBJECTS_ALPHANUMERIC WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='GRADE' AND (OBJECTS_ALPHANUMERIC_VALUE='Excellent' OR OBJECTS_ALPHANUMERIC_VALUE IS NULL) ) AS t1 ON t0.OBJECTS_ID = t1.OBJECTS_ID ) LEFT JOIN ( SELECT * FROM OBJECTS_ALPHANUMERIC WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='OWNER' AND NOT OBJECTS_ALPHANUMERIC_VALUE='John Smith' ) AS t2 ON t0.OBJECTS_ID = t2.OBJECTS_ID ) LEFT JOIN ( SELECT * FROM OBJECTS_DATE WHERE OBJECTS_DATE_FIELD_ID='EXHIBIT_END_DATE' ) AS t3 ON t0.OBJECTS_ID = t3.OBJECTS_ID ORDER BY 3,2; In the previous example, the user would have asked for 1- the item's description, it's owner it's exhibit end date 2- Objects that are Jewelry 3- Objects in excellent condition (grade) or unspecified 4- Objects not owned by John Smith 5- Sorted by exhibit end date and owner name The "relationship" between OBJECTS and OBJECTS_ALPHANUMERIC is 0,N and the "relationship" between OBJECTS and OBJECTS_DATE is also 0,N. The previous query worked just fine in Oracle and Access, but not in SQLite. SQLite might simply not be support it, that is what I want to know. If it's a bug, a feature SQLite will support in the future (how close in the future) or a feature that will never be supported. Thank you all for your time Simon Berthiaume On Mon, 2004-01-19 at 14:24, Kurt Welgehausen wrote: > If you look at the SQLite grammar in lang.html, you'll see > that parentheses are not allowed around a table-list. That's > why you're getting an error. > > If you remove either of the first 2 left parens (and its > corresponding right paren), the query will work, but the > outer select and the first subselect serve no purpose. It's > hard to predict performance just by reading a query, but this > looks pretty inefficient. How about > > select * from > INSCLAIMS t0 > left join > (select * from INSCLAIMS_CONCAT > where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1 > on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID; > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] >
Re: [sqlite] Query problem
Actualy none, there was an error in the statement I copied. But the following works on Access (ADO) and Oracle as well, but not on SQLite. Since I don't have access to any other RDBMS, I couldn't test others. SELECT * FROM ( ( SELECT * from INSCLAIMS ) AS T0 LEFT JOIN ( SELECT * FROM INSCLAIMS_CONCAT WHERE ( INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' ) ) AS T1 ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID ) ; Simon Berthiaume On Mon, 2004-01-19 at 12:52, D. Richard Hipp wrote: > Simon Berthiaume wrote: > > SELECT * > > FROM > >( > > ( > > SELECT * from INSCLAIMS > > ) AS T0 > > LEFT JOIN > > ( > > SELECT * FROM INSCLAIMS_CONCAT WHERE > > ( > > INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' > > ) > > ) AS T1 > > ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID > >) AS foo > > ; > > > > On what RDBMSes does the above query actually work? >
[sqlite] Query problem
Yep, it's me again (no I haven't solved my other problem yet). This time
I was wondering why the SQL engine of SQLite works fine with the
following query:
SELECT *
FROM
(
SELECT * from INSCLAIMS
) AS T0
LEFT JOIN
(
SELECT * FROM INSCLAIMS_CONCAT WHERE
(
INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO'
)
) AS T1
ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID
;
But returns an error (SQL error: near "(": syntax error) with the
following query:
SELECT *
FROM
(
(
SELECT * from INSCLAIMS
) AS T0
LEFT JOIN
(
SELECT * FROM INSCLAIMS_CONCAT WHERE
(
INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO'
)
) AS T1
ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID
) AS foo
;
So why do I have this behavior, does SQLite only support a maximum
degree of "inner-ness" in queries?
Simon Berthiaume
[sqlite] Bug or "feature" in sqlite_exec?
I'm having a problem with function sqlite_exec. I call sqlite_exec with the followinf SQL query with the SHOW_DATATYPE pragma set ON: SELECT T0.OBJECTS_ID, T0.OBJECTS_COLTYPE, T0.OBJECTS_OBJTYPE, T0.OBJECTS_ORIGIN, T0.OBJECTS_DATE, T1.OBJECTS_CONCAT_VALUE FROM OBJECTS AS T0 LEFT JOIN ( SELECT * FROM OBJECTS_CONCAT WHERE ( OBJECTS_CONCAT_FIELD_ID = 'OBJECTS_ARTIST' ) ) AS T1 ON T1.OBJECTS_ID = T0.OBJECTS_ID WHERE T0.OBJECTS_DELETED = 0 AND T0.OBJECTS_ID IN (1,2) ORDER BY 2 ASC; When my callback fct is called I get the good column types for all "T0.*" columns but NULL from the "T1.OBJECTS_CONCAT_VALUE" column type. I would have understood if the given column was a constant value or the value returned by a SQL fct call, but this is a direct map of an inner query column. I know I could write the query differently, but this query is generated by C++ code and not hand written. If we alter the code, some more (much more!) complex queries will either not work or be slow as hell due to the tedious amount of JOIN and the number of records in each table. By using inner queries, we found the performance to be much greater and we understand it may come at the expense of RAM usage. So please no comment on how I could re-write the query. If anyone can tell me why sqlite_exec behaves like that, it would be appreciated. Simon Berthiaume
