Re: [sqlite] Left join help.

Many thanks to you all, I really appraciate your helpfulness, Danilo 2013/4/26 Clemens Ladisch > Hick Gunter wrote: > > Actually "CROSS" just forces SQLite to use the tables in the order > > specified. > > This is an SQLite optimization extension. I used CROSS because this is > the only explic

[sqlite] Left join help.

Hi to all, I'm looking for a query on the following schema: PRAGMA foreign_keys=ON; BEGIN TRANSACTION; CREATE TABLE subj ( sub_id INTEGER PRIMARY KEY, -- 00 sub_descr TEXT DEFAULT NULL -- 01 ); INSERT INTO "subj" VALUES(1,'Paul'); INSERT INTO "subj" VALUES(2,'John'); CREATE TABLE itx ( it

[sqlite] Handling error messages using ON CONFLICT clause.

Hi to all, I'd like to use a customized error message when occur a CONSTRAINT violation. The only way seems the use of RAISE() function but it's applicable only in a TRIGGER. So, if my table is: CREATE TABLE t1( a INTEGER PRIMARY KEY, b TEXT DEFAULT NULL, UNIQUE(b)); How to raise a custo

Re: [sqlite] How to JOIN...

Thanks, your solution works better in case a student has subscribed a course and has no card. Just add the following SQL statement to the FILE: INSERT INTO A VALUES(3,'Jack','Bridge'); INSERT INTO A2B VALUES(5,3,3); to prove it. 2012/2/22 Igor Tandetnik : > On 2/22/2012 1

Re: [sqlite] How to JOIN...

Thanks! Sigh, I need more research on JOINs I need more research on JOINs, I need more research on JOINs, I need more research on JOINs, I need more research on JOINs, I need more research on JOINs, I need more research on JOINs, I need more research on JOINs, I need more research on JOINs. 2012/2

[sqlite] How to JOIN...

...two views. According to the following SQL statement(see below) I'm going to create a table 'A' storing student's name, table 'B' storing courses and table D storing cards. Furthermore, it creates two pivot tables A2B and A2D to refer courses and cards. I'd like to query the DB to get that result

Re: [sqlite] SQL Statement Help(selecting days).

Amazing! Thanks a lot. 2011/5/18 Igor Tandetnik : > select startGap, min(endGap) from > ( >   select date(e1.endDate, '+1 day') startGap, date(e2.startDate, '-1 > day') endGap >   from (select startDate, endDate from events >         union all >         select '' startDate, date('2011-01-01', '-1

Re: [sqlite] SQL Statement Help(selecting days).

Thanks again. 2011/5/18 Petite Abeille : > > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote: > (1) materialize the calendar as a table > (2) virtualize the calendar as a generator > ___ sqlite-users mailing list sqlite-users@

Re: [sqlite] SQL Statement Help(selecting days).

Thanks Petite, I get it, great. Only another quick question, How can I simulate a calendar table(maybe using the strftime funtion)? Danilo 2011/5/18 Petite Abeille : > > On May 18, 2011, at 10:17 PM, Pavel Ivanov wrote: > >> SQL wasn't intended for and can't solve such tasks. > > Of course it can.

[sqlite] SQL Statement Help(selecting days).

Hi to all, I need help to build a statement in order to select all days free from events in a specific time range. E.g.: - Range(-MM-DD) from 2011-01-01 to 2011-02-01. - Event 1 from 2011-01-02 to 2011-01-08 - Event 2 from 2011-01-06 to 2011-01-12 - Event 3 from 2011-01-18 to 2011-01-21 Resul

Re: [sqlite] Difference between times.

Thanks Simon, GREAT solution!!! A very interesting sintax. 2010/10/21 Simon Davies > On 21 October 2010 11:58, Danilo Cicerone wrote: > > Thanks Simon, but I've the following situation: > > > . > . > . > > The SQL query should be something like that: > >

Re: [sqlite] Difference between times.

:00:00' and dateEnd <= '2010-01-21 00:00:00'; this statement doesn't work due the "and dateEnd <= '2010-01-21 00:00:00';" that cut out the record. 2010/10/21 Simon Davies > On 21 October 2010 11:03, Danilo Cicerone wrote

[sqlite] Difference between times.

Hi to all, I'd like to calculate hours and minutes having the following situation where A and B are query, B and C are the data stored in a table: ->Time A B | | C D | | A = '2010-01-20 09:00:00' B = '2010-01-21

[sqlite] Use of strftime in a CREATE INDEX sintax.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

[sqlite] Index creation using 'strftime'.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

[sqlite] Index creation using 'strftime'.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;