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.
sqlite-users mailing list