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

Reply via email to