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
