Re: [sqlite] How to join 3 tables?

On Wed, 2009-10-07 at 12:10 +0800, Kermit Mei wrote: > Hello, I have three tables like this: > > HomeDev(id,...,text) > ZPhDev(id,...,HomeDevId) > ZGroupItem(id,...,groupId,ZPhDevId) > > Now, I want to get the items like this: > > ZPhDev.id, and its corresponding HomeDev.text, the relation is

Re: [sqlite] How to join 3 tables?

Kermit Mei wrote: > How can I do it in only one sql command? I wrote it like this, but it > can't run: > sqlite> SELECT ZGroupItem.ZPhDevId HomeDev.text FROM ZGroupItem >...> INNER JOIN ZPhDev ON ZGroupItem.ZPhDevId = ZPhDev.id >...> WHERE ZGroupItem.groupId = 1; > SQL error: near ".":

[sqlite] How to join 3 tables?

Hello, I have three tables like this: HomeDev(id,...,text) ZPhDev(id,...,HomeDevId) ZGroupItem(id,...,groupId,ZPhDevId) Now, I want to get the items like this: ZPhDev.id, and its corresponding HomeDev.text, the relation is like this: 1. Get ZGroupItem.ZPhDevId When I know groupId 2. Get

Re: [sqlite] Clarification needed for a syntax

´¯¯¯ >SQLite tolerates this violation by picking the first >column from the first row as the value of such an expression. There is >no n-tuple here. `--- That explains it very clearly now. Point taken. Thanks. ___ sqlite-users mailing list

Re: [sqlite] Multiple NATURAL JOIN precedence?

Kristoffer Danielsson wrote: > All my tables have unique column names, so I tend to use "NATURAL JOIN" for > my joins (all join-columns are indexed). When I have multiple tables, I > sometimes get incredibly slow queries (not sure if they even terminate). If all of your tables have unique column

Re: [sqlite] Clarification needed for a syntax

Jean-Christophe Deschamps wrote: > What suprises me is that a select of 1 field yields a field (not a > 1-tuple containing 1 field) but then a select of n > 1 fields yields a > n-tuple, not a sequence of n fields. A sub-select returning more than one column or one row is actually illegal when

Re: [sqlite] Clarification needed for a syntax

> > INSERT INTO t (a, b, c, d) VALUES ('aa', 'bb', (SELECT c, d FROM t > > WHERE )); > > with guaranteed to select exactly one row. > >I don't know of any DBMS where this would be valid. It seems so. >INSERT INTO t(a, b, c, d) >SELECT 'aa', 'bb', c, d FROM t WHERE ; My mistake: I had

Re: [sqlite] Clarification needed for a syntax

Jean-Christophe Deschamps wrote: > I've been surprised that the following syntax doesn't work and returns > "3 values for 4 columns" diagnose message. I'm just asking by > curiosity. > > INSERT INTO t (a, b, c, d) VALUES ('aa', 'bb', (SELECT c, d FROM t > WHERE )); > with

[sqlite] Clarification needed for a syntax

Hi all, I've been surprised that the following syntax doesn't work and returns "3 values for 4 columns" diagnose message. I'm just asking by curiosity. INSERT INTO t (a, b, c, d) VALUES ('aa', 'bb', (SELECT c, d FROM t WHERE )); with guaranteed to select exactly one row. I thought

[sqlite] Multiple NATURAL JOIN precedence?

All my tables have unique column names, so I tend to use "NATURAL JOIN" for my joins (all join-columns are indexed). When I have multiple tables, I sometimes get incredibly slow queries (not sure if they even terminate). E.g. SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL

Re: [sqlite] [Retrieved]Re: DB Corruption

On 5 Oct 2009, at 2:59pm, Reusche, Andrew wrote: > We are trying to programmatically update the primary keys on this > table, in order to sync it up with a remote database. In the event > that > this needs to occur, we typically need to take a chunk of records in > this table, and increment

Re: [sqlite] [Retrieved]Re: DB Corruption

On Oct 6, 2009, at 1:19 PM, Reusche, Andrew wrote: > When I run pragma synchronous" I receive a 2. Documentation says this > is the value for "Full" synchronous mode. I've checked this using the > routine through our application, and also using sqlite3.exe. This > is on > a w32 system, if

Re: [sqlite] Wrong column decltype

Artem Avramenko wrote: > Another example: > > CREATE TABLE a ( > id int, > flags int > ); > INSERT INTO a VALUES(1, 0); > INSERT INTO a VALUES(2, 1); > > CREATE TABLE b AS > SELECT id, (flags & 1) as flag FROM a where (flags & 1) = 1; > > > In following SQL

[sqlite] Wrong column decltype

Another example: CREATE TABLE a ( id int, flags int ); INSERT INTO a VALUES(1, 0); INSERT INTO a VALUES(2, 1); CREATE TABLE b AS SELECT id, (flags & 1) as flag FROM a where (flags & 1) = 1; In following SQL sqlite3_column_type returns null affinity: SELECT b.flag FROM a LEFT JOIN b ON

Re: [sqlite] Wrong column decltype

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Pavel Ivanov wrote: > Changing of declared datatype in CREATE TABLE AS SELECT is intended > and was made in 3.6.15 (look here > http://www.sqlite.org/releaselog/3_6_15.html). It is also mentioned in http://www.sqlite.org/datatype3.html at the end of

[sqlite] ANN: StepSqlite PL/SQL Compiler 3.4a released

Hi! Metatranz StepSqlite Team proudly announces the release of StepSqlite 3.4a - a power-packed PL/SQL compiler for SQLite. This version adds two great new features and major improvements to error reporting and built-in package support. Join the hundreds who have discovered StepSqlite - the

Re: [sqlite] Tedious CSV import question

Emyr Thomas wrote: > On Oct 6, 12:50 pm, Pavel Ivanov wrote: >> I think you need to change your last compilation command line to this: >> >> gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib >> -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o > > That

Re: [sqlite] Wrong column decltype

First of all be careful with your wording. "datetime" in your case is not affinity, it's declared datatype. Affinity in both of your tables is NUMERIC, so it doesn't change. Changing of declared datatype in CREATE TABLE AS SELECT is intended and was made in 3.6.15 (look here

Re: [sqlite] Tedious CSV import question

On Tue, Oct 06, 2009 at 07:50:52AM -0400, Pavel Ivanov scratched on the wall: > I think you need to change your last compilation command line to this: > > gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib > -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o Neither

[sqlite] Wrong column decltype

I have following script: CREATE TABLE a (d datetime); INSERT INTO a insert into('01-01-01'); CREATE TABLE b AS SELECT d FROM a; Then I use this sql: SELECT d FROM b; In SQLite version 3.6.4 the "datetime" affinity is persisted, but from version 3.6.16 (maybe earlier) the affinity is lost

Re: [sqlite] Tedious CSV import question

On Oct 6, 12:50 pm, Pavel Ivanov wrote: > I think you need to change your last compilation command line to this: > > gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib > -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o That didn't quite do it, but after

Re: [sqlite] Problem with PAGE_SIZE and CACHE_SIZE in the sqlite356 c-api

On Oct 6, 2009, at 8:00 AM, Koston, Thorsten (ICT) wrote: > Hello, > > i have a problem with the sqlite 3.5.6 C-API. > I want to change the page_size and the cache_size. > > I edit this values in the sqlite3.c file. Then i build the sqlite new > and start my application, whick uses sqlite. > In

[sqlite] Problem with PAGE_SIZE and CACHE_SIZE in the sqlite356 c-api

Hello, i have a problem with the sqlite 3.5.6 C-API. I want to change the page_size and the cache_size. I edit this values in the sqlite3.c file. Then i build the sqlite new and start my application, whick uses sqlite. In the application i copy a table of a db to another db (so open an new db,

Re: [sqlite] Tedious CSV import question

I think you need to change your last compilation command line to this: gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o Pavel On Tue, Oct 6, 2009 at 6:16 AM, Emyr Thomas wrote: > On Oct 6, 10:52 

Re: [sqlite] "UPDATE FROM"

> Is there somewhere that it is explained how to do this in SQLITE? There's no way for this in SQLite. You have to rewrite your update statement to be for example like UPDATE ... WHERE id IN (SELECT ... ) > I want to add a column to T1 for the appropriate sex for a given ID from T2. Sorry, I

Re: [sqlite] [Retrieved]Re: DB Corruption

On Oct 5, 2009, at 9:59 AM, Reusche, Andrew wrote: > Thanks. We are beyond the point of recovering data at this point, we > will just start over with a fresh db. Two questions pertaining to > what > may have caused this: > > 1) We are trying to programmatically update the primary keys on

[sqlite] "UPDATE FROM"

In Postgresql, there is a (nonstandard) way to specify multiple tables in an update clause, useful when updating a table with columns based on an inner join idea. Is there somewhere that it is explained how to do this in SQLITE? Example Table 1 has ID, age Table 2 has ID, sex I want to add

Re: [sqlite] [Retrieved]Re: DB Corruption

Thanks. We are beyond the point of recovering data at this point, we will just start over with a fresh db. Two questions pertaining to what may have caused this: 1) We are trying to programmatically update the primary keys on this table, in order to sync it up with a remote database. In the

Re: [sqlite] Tedious CSV import question

On Oct 6, 11:16 am, Emyr Thomas wrote: > Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/ > local/include and -L/opt/local/lib to the make file. It gets further, > but now I get the following error: Of course I meant -I/opt/local/include, not

Re: [sqlite] Tedious CSV import question

On Oct 6, 10:52 am, Dan Kennedy wrote: > You have the most recent version installed. But perhaps the pre- > processor > is finding an older sqlite3.h. Try adding -I/usr/local/include to the > gcc command. Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/

Re: [sqlite] Tedious CSV import question

On Oct 6, 2009, at 4:40 PM, Emyr Thomas wrote: > On Oct 5, 8:09 pm, Nuno Lucas wrote: >> Emyr Thomas wrote: >>> Could you please provide some info as to how you would build this as >>> an extension module on Linux? >> >> "make" ? >> >> It would be easier if you stated why

Re: [sqlite] Tedious CSV import question

On Oct 5, 8:09 pm, Nuno Lucas wrote: > Emyr Thomas wrote: > > Could you please provide some info as to how you would build this as > > an extension module on Linux? > > "make" ? > > It would be easier if you stated why that doesn't work for you. Apologies, my question was very