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 (
CREATE TABLE ATTRIBUTES (
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
But is it possible to write a single query that will present results from both
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.
sqlite-users mailing list