I want to add the results of two separate counting SQlite queries. Suppose I have 2 tables named entries
and scores
and have 2 queries:
SELECT COUNT(1) FROM entries WHERE
key NOT IN (SELECT key FROM scores)
SELECT COUNT(1) FROM scores WHERE
value <= threshold
Maybe I could do something like this to get the sum of their results:
SELECT COUNT(1) from (
SELECT key FROM entries WHERE
key NOT IN (SELECT key FROM scores)
UNION ALL
SELECT key FROM scores WHERE
value <= threshold
)
But is this a little too inefficient? This is called pretty often and may interfere with the UI's smoothness.
Thank you.
[EDIT] What I'm actually trying to do:
I'm making an app to help learning vocabulary. The entries
table keeps 'static' data about word type, definition, etc. The scores
table keeps information about how well you've learned the words (e.g. performance, scheduled next review time)
To check for the number of remaining words to learn/review, I count how many words do not exist in the scores
table yet (i.e. never touched) or when the accumulated score is pretty low (i.e. needs reviewing).
The reason I don't merge those 2 tables into 1 (which would make my life much easier) is because sometimes I need to update the entries
table either by inserting new words, deleting a few words, or updating their content, and I haven't found a simple way to do that. If I simply do INSERT OR REPLACE
, I will lose the information about scores.