sqlite SQL query for unprocessed rows

Question

Hi,

I'm not quite even sure where / what to search for - so apologies if this is a trivial thing that has been asked before!

I have two tables in sqlite:

table_A = [id, value1, value2]
table_A$foo = [id, foo(value1), foo(value2)]
table_A$bar = [id, bar(value1), bar(value2)]

Where foo() / bar() are arbitrary functions not really relevant here

Now at the moment, I do:

select * from table_A

And use this cursor to compute all the rows for each of the derivative tables. If something goes wrong (or I add new rows to table_A), i'd like a way to be able to compute (within SQL, rather than in python) which rows are already present in table_A$foo etc. and so just select the remaining (so like a AND NOT)to compute foo() and bar() - i should be able to do this on the ID col, as these remain the same.

Wondering if there is a way to do this in sqlite, which I imagine would be quicker than trying to rig this up in python.

Many thanks!

Answer 1

I don't understand if you consider a match based on value1 columns matching, or a combination of all three columns...

Using EXISTS to find those that are already present:

SELECT *
  FROM TABLE_A a
 WHERE EXISTS(SELECT NULL
                FROM TABLE_A$foo f
               WHERE a.id = f.id
                 AND a.value1 = f.value1
                 AND a.value2 = f.value2)

Using EXISTS to find those that are not present:

SELECT *
  FROM TABLE_A a
 WHERE NOT EXISTS(SELECT NULL
                    FROM TABLE_A$foo f
                   WHERE a.id = f.id
                     AND a.value1 = f.value1
                     AND a.value2 = f.value2)