Need help with SQL query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Need help with SQL query

Vikas Aditya
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 ATTRIBUTES (
ID INTEGER,
ITEM_ID INTEGER,
KEY TEXT
VALUE TEXT
);

For a single row in item table, we can have multiple rows in attributes table. It is a generic Key/Value attributes for an item. ITEM_ID in 2nd table is the ID of an item in 1st table.

We want to select Items sorted by VALUE in the Attribute table.

I have tried following query

SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and key=“abc” SORT BY VALUE;

This works and items are sorted by value but naturally it only selects items that have key/value specified for an item. We also have some items that have missing attributes, so key/value row for that item is missing and above query will not select those items. We can write a different query that can select all items where key is missing. For example:

SELECT ITEM.ID, FIELD0 from ITEM, ATTRIBUTES where ITEM.ID not in (SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and key==“abc”)


But is it possible to write a single query that will present results from both queries above?
Our use case is that we are trying to implement sorting functionality in our app and for items that don’t have a key/value attribute, we want to treat value as NULL or “”  but still return the item. Because results are sorted by VALUE, these items with missing key/value will be at beginning of result set. Because we also want to add LIMIT and OFFSET, we think a single query is better solution versus trying to run two queries and trying to merge two results.

Thanks,
Vikas

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need help with SQL query

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 TEXT,
> FIELD3 TEXT,
> FIELD4 TEXT
> );
>
> CREATE TABLE ATTRIBUTES (
> ID INTEGER,
> ITEM_ID INTEGER,
> KEY TEXT
> VALUE TEXT
> );
>
> For a single row in item table, we can have multiple rows in attributes table. It is a generic Key/Value attributes for an item. ITEM_ID in 2nd table is the ID of an item in 1st table.
>
> We want to select Items sorted by VALUE in the Attribute table.
>
> I have tried following query
>
> SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and key=“abc” SORT BY VALUE;
>
> This works and items are sorted by value but naturally it only selects items that have key/value specified for an item. We also have some items that have missing attributes, so key/value row for that item is missing and above query will not select those items. We can write a different query that can select all items where key is missing. For example:
>
> SELECT ITEM.ID, FIELD0 from ITEM, ATTRIBUTES where ITEM.ID not in (SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and key==“abc”)
>
>
> But is it possible to write a single query that will present results from both queries above?
> Our use case is that we are trying to implement sorting functionality in our app and for items that don’t have a key/value attribute, we want to treat value as NULL or “”  but still return the item. Because results are sorted by VALUE, these items with missing key/value will be at beginning of result set. Because we also want to add LIMIT and OFFSET, we think a single query is better solution versus trying to run two queries and trying to merge two results.

You want LEFT JOIN.

Something like:

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
  SORT BY VALUE;


Note 1:  Left join will list all the values from the first table (the
LEFT table) and add results where possible from the second (RIGHT)
table, else the values will be NULL for it.
Note 2: ISNULL(x,a) Will output the value of x, unless it is NULL, in
which case it will show a.
Note 3:  The strings in SQL has single quotes (like 'abc'), only
identifiers get double quotes.
Note 4: LIMIT and OFFSET is a very bad way to do paging (in case that's
what you are planning). It's good for limiting the size of a query, but
offset has to reproduce the entire query every time and wait for the
offset number of rows to pass before it can jump in and start adding
rows to the output - it's not really "remembering" where it left off.
You can do that better with temporary tables containing a result set and
then stepping through those tables based on a key.

Cheers,
Ryan



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need help with SQL query

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, it's 6am and I need to go to bed still...)

>
> Note 1:  Left join will list all the values from the first table (the
> LEFT table) and add results where possible from the second (RIGHT)
> table, else the values will be NULL for it.
> Note 2: ISNULL(x,a) Will output the value of x, unless it is NULL, in
> which case it will show a.
> Note 3:  The strings in SQL has single quotes (like 'abc'), only
> identifiers get double quotes.
> Note 4: LIMIT and OFFSET is a very bad way to do paging (in case
> that's what you are planning). It's good for limiting the size of a
> query, but offset has to reproduce the entire query every time and
> wait for the offset number of rows to pass before it can jump in and
> start adding rows to the output - it's not really "remembering" where
> it left off. You can do that better with temporary tables containing a
> result set and then stepping through those tables based on a key.
>
> Cheers,
> Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need help with SQL query

Kees Nuyt
In reply to this post by Vikas Aditya
On Sun, 10 Sep 2017 20:35:16 -0700, Vikas Aditya
<[hidden email]> 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 to the suggestions of Ryan Smith, I would suggest a few
table definition optimizations:

> CREATE TABLE ITEM (
> ID INTEGER,
> FIELD0 TEXT
> FIELD1 TEXT,
> FIELD3 TEXT,
> FIELD4 TEXT
> );

By adding 'PRIMARY KEY to the ÍD column, it becomes an alias for
the internal ROWID column, saving space. Also, to reference a
parent table, the reference needs to point to a unique column.
A primary key fulfills that requirement.

CREATE TABLE ITEM (
ID INTEGER PRIMARY KEY,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

> CREATE TABLE ATTRIBUTES (
> ID INTEGER PRIMARY KEY,
> ITEM_ID INTEGER,
> KEY TEXT,
> VALUE TEXT
> );

Do the same for the primary key of the attributes table.
Add a foreign key constraint to formalize the relation
between the parent and child table. This provides
"referential integrity".
The index will often speed up JOIN operations.

CREATE TABLE ATTRIBUTES (
  ID INTEGER PRIMARY KEY,
  ITEM_ID INTEGER REFERENCES ATTRIBUTES ON DELETE CASCADE,
  KEY   TEXT,
  VALUE TEXT
);
CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Even better, the ID column can be left out, and a different
primary key will enforce that the same KEY can only be used once
for any ITEM_ID:
 
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);

Note:
Foreign key constraints are only enforced when
   PRAGMA foreign_keys=on;
is executed after opening the database, before any
INSERT/UPDATE/DELETE statement is issued.

HTH

--
Regards,

Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need help with SQL query

Kees Nuyt
On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt <[hidden email]>
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);

Correction:
In this construct, it makes no sense to create the index
attr_item_id, because the ITEM_ID is the first column
of the primary key (which is indexed implicitly).

So, you can leave out the CREATE INDEX attr_item_id
statement in this case.

--
Regards,

Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need help with SQL query

John Gillespie-2
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, Kees Nuyt <[hidden email]> wrote:

> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt <[hidden email]>
> 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);
>
> Correction:
> In this construct, it makes no sense to create the index
> attr_item_id, because the ITEM_ID is the first column
> of the primary key (which is indexed implicitly).
>
> So, you can leave out the CREATE INDEX attr_item_id
> statement in this case.
>
> --
> Regards,
>
> Kees Nuyt
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need help with SQL query

Kees Nuyt
On Fri, 22 Sep 2017 10:54:21 +0100, John G
<[hidden email]> 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,
>    PRIMARY KEY (ITEM_ID,KEY)
>  ) WITHOUT ROWID;
>
>John G

Yes, you are right. I didn't pay enough attention typing that
code. My apologies for any confusion that may have caused.

--
Regards,
Kees Nuyt



>On 11 September 2017 at 13:11, Kees Nuyt <[hidden email]> wrote:
>
>> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt <[hidden email]>
>> 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);
>>
>> Correction:
>> In this construct, it makes no sense to create the index
>> attr_item_id, because the ITEM_ID is the first column
>> of the primary key (which is indexed implicitly).
>>
>> So, you can leave out the CREATE INDEX attr_item_id
>> statement in this case.
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users