Re: [sqlite] Need help with SQL query

2017-09-22 Thread Kees Nuyt
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

2017-09-22 Thread John G
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

2017-09-11 Thread Kees Nuyt
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

2017-09-11 Thread Kees Nuyt
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

2017-09-10 Thread R Smith
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

2017-09-10 Thread R Smith
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