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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
