Re: [sqlite] Need help with SQL query

On Fri, 22 Sep 2017 10:54:21 +0100, John G wrote: >I know this is an older thread, but shouldn't that reference be on the ITEM >table ? So ... > >CREATE TABLE ATTRIBUTES ( >ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE, > KEY TEXT, >VALUE TEXT, >

Re: [sqlite] Need help with SQL query

I know this is an older thread, but shouldn't that reference be on the ITEM table ? So ... CREATE TABLE ATTRIBUTES ( ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE, KEY TEXT, VALUE TEXT, PRIMARY KEY (ITEM_ID,KEY) ) WITHOUT ROWID; John G On 11 September 2017 at 13:11,

Re: [sqlite] Need help with SQL query

On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt wrote: > CREATE TABLE ATTRIBUTES ( > ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE, > KEY TEXT, > VALUE TEXT, > PRIMARY KEY (ITEM_ID,KEY) > ) WITHOUT ROWID; > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Re: [sqlite] Need help with SQL query

On Sun, 10 Sep 2017 20:35:16 -0700, Vikas Aditya wrote: > Hi All, > > I need some help in figuring our right query syntax for querying > items from two tables. We have two tables. One of the table has > list of items. And Second table has additional attributes. Adding

Re: [sqlite] Need help with SQL query

Correction: On 2017/09/11 6:43 AM, R Smith wrote: SELECT I.ID, ISNULL(A.VALUE,'[No Value]')   FROM ITEM AS I   LEFT JOIN ATTRIBUTES AS A ON A.ITEM_ID = I.ID  WHERE A.key='abc' OR A.key IS NULL  ORDER BY A.VALUE; There is of course no such thing as SORT BY in SQL, it's ORDER BY. (Forgive me,

Re: [sqlite] Need help with SQL query

On 2017/09/11 5:35 AM, Vikas Aditya wrote: Hi All, I need some help in figuring our right query syntax for querying items from two tables. We have two tables. One of the table has list of items. And Second table has additional attributes. CREATE TABLE ITEM ( ID INTEGER, FIELD0 TEXT FIELD1

[sqlite] Need help with SQL query

Hi All, I need some help in figuring our right query syntax for querying items from two tables. We have two tables. One of the table has list of items. And Second table has additional attributes. CREATE TABLE ITEM ( ID INTEGER, FIELD0 TEXT FIELD1 TEXT, FIELD3 TEXT, FIELD4 TEXT ); CREATE TABLE

Re: [sqlite] need help with a query using datetime

Black, Michael (IS) wrote: > sqlite> create table t(d date); > sqlite> insert into t values('2011-12-31 09:00'); > sqlite> insert into t values('2011-12-31 12:15'); > sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t; > 2011-12-31 09:00|2011-12-30 21:00 >

Re: [sqlite] need help with a query using datetime

On 17/06/2011 12:10, looki wrote: > First column holds the givin datetime from my table and the second column > should show the datetime from first row but 12 hours before. for example: > > '2011-12-31 09:00' '2011-12-30 21:00' > '2011-12-31 12:15' '2011-12-30 00:15' > ... > > looks simple but

Re: [sqlite] need help with a query using datetime

lack Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of looki [looki1...@yahoo.com] Sent: Friday, June 17, 2011 6:10 AM To: sqlite-users@sqlite.org Subject: EXT :[

[sqlite] need help with a query using datetime

Hi, i have a table which holds dates like '2011-12-31 09:00' and i now want to write a query which gives me 2 columns. First column holds the givin datetime from my table and the second column should show the datetime from first row but 12 hours before. for example: '2011-12-31 09:00'

Re: [sqlite] Need help constructing a query

Jörgen Hägglund wrote: > Hi all! > I have three tables, one containing IP addresses as integers, lets > call > it 'base'. > A second table containing IP ranges and the country code the range > belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as >

Re: [sqlite] Need help constructing a query

On Wed, Nov 11, 2009 at 09:36:41AM -0600, P Kishor scratched on the wall: > 2009/11/11 Jörgen Hägglund : > > Hi all! > > I have three tables, one containing IP addresses as integers, lets call > > it 'base'. > > A second table containing IP ranges and the country code

Re: [sqlite] Need help constructing a query

2009/11/11 Jörgen Hägglund : > Hi all! > I have three tables, one containing IP addresses as integers, lets call > it 'base'. > A second table containing IP ranges and the country code the range > belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as >

[sqlite] Need help constructing a query

Hi all! I have three tables, one containing IP addresses as integers, lets call it 'base'. A second table containing IP ranges and the country code the range belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as integers and CC as text. Then a third, 'Exclude', containing

Re: Re: [sqlite] Need help on build query.

Thanks --- [EMAIL PROTECTED] wrote: > "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > > Artem Yankovskiy > > > wrote: > > > I know about DROP TABLE. > > > I have not knew when compatibility DROP TABLE > and > > > SELECT... > > > > > > DROP TABLE (select name from sqlite_master > where...) > > >

Re: [sqlite] Need help on build query.

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > On 2/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > > Igor is correct - you cannot do that now. But I have sometimes > > wondered if it would be nice to add an exec() function to SQLite > > that would recursively invoke its argument as an

Re: [sqlite] Need help on build query.

On 2/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Igor is correct - you cannot do that now. But I have sometimes wondered if it would be nice to add an exec() function to SQLite that would recursively invoke its argument as an SQL script. For example: SELECT exec('DELETE FROM ' ||

Re: [sqlite] Need help on build query.

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Artem Yankovskiy > wrote: > > I know about DROP TABLE. > > I have not knew when compatibility DROP TABLE and > > SELECT... > > > > DROP TABLE (select name from sqlite_master where...) > > did not work. > > Can I build resembling query? > > No. You

[sqlite] Need help on build query.

Hello. I like delete some of table. For example: I can run query: select name from sqlite_master where name like 'prefix_%'; Can I delete found tables? Using only sql. It's real? Best regards, Artem Yankovskiy

[sqlite] Need help with a query

Hi all, I guess this isn't really all that complex, but I just can't think of a query that does what I need. Here's an example table: CREATE TABLE t1 (fname TEXT, vers TEXT, chng INT, UNIQUE(fname,vers)); INSERT INTO t1 VALUES('file1', '1', 0); INSERT INTO t1 VALUES('file1', '2', 1); INSERT INTO