What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable WHERE somevalue IN ('THIS', 'THAT', 'OTHER THING') ... except it's sort of the inverse:
Ideally the statement would give me everything from ('FOO', 'BAR', 'BAZ') NOT IN sometable.somecolumn then I could use that as my check (assert that my SQL query returns an empty set or take the resulting set and complain that each of these is an "unrecognized" key). (A brute force approach would be to insert all of the terms/tags/items into a temporary, single column table and perform a JOIN ON my actual table's column for the set of rows which are NOT IN it.
I'm sure I'm missing some syntactic trick that'll seem obvious once I see an example.
In this particular case my table structures are something like:
CREATE TABLE items (id INTEGER PRIMARY KEY, item TEXT UNIQUE NOT NULL); CREATE TABLE tags (tag_id INTEGER PRIMARY KEY, tag TEXT UNIQUE NOT NULL); CREATE TABLE item_tag (item_id integer, tag_id integer, FOREIGN KEY(item_id) REFERENCES items(id), FOREIGN KEY(tag_id) REFERENCES tags(tag_id), PRIMARY KEY (item_id, tag_id));
... but I'm simply trying to come up with a template for taking an arbitrary number of "tags" and ensure that all of them are valid entries the "tags.tag" column. (The application will have to support arbitrarily complex queries for subsets of these tags to return DISTINCT subsets of the items; but I want to raise an error on any non-existent tag before building the JOIN's WHERE expression).
Obviously I could just loop over the tags doing a separate SELECT tag_id FROM tags where tag=? ... entailing numerous round trips to the database. But this seems silly when I could send all of them to some sort of query in a single statement.