I'm a relative novice to sqlite (or sql in general), but I do understand the value of variable substitution when building queries:
e.g. set someValue 23 db eval {SELECT something FROM myTable WHERE value=$someValue} It feels like there should be a comparable solution for IN, passing instead a Tcl list, but I've failed to find it documented or had any trial-and-errorr success. e.g. set someTags {1 23 45} db eval {SELECT something FROM myTable WHERE value IN ($someTags)} If that's not a supported feature, what would be the recommended and safe way of building the query? As that's just a detail of a novice's attempt to implement a part of something that most likely could more correctly be done in a different way, here's more detail of the overall plan (a light-weight iTunes-like library): Given a trio of tables created as: db eval { CREATE TABLE IF NOT EXISTS media( id INTEGER PRIMARY KEY AUTOINCREMENT, filename TEXT -- further fields removed ); CREATE TABLE IF NOT EXISTS tags( id INTEGER PRIMARY KEY AUTOINCREMENT, tag TEXT, unique(tag) ); CREATE TABLE IF NOT EXISTS tagUsage( mediaId INTEGER, tagId INTEGER, unique(mediaId,tagId) ); CREATE TRIGGER IF NOT EXISTS tagCleanup AFTER DELETE ON tags BEGIN DELETE FROM tagUsage WHERE tagUsage.tagId=OLD.id; END; } 'media' describes an audio or video file. 'tags' is just a list of words 'tagUsage' associates tags to media (a media can have zero or more tags, a tag can belong to zero or more media). At some point, I'm going to want to write queries that answer the questions: 1. What media files have at least *one* of this set of tags? 2. What media files have *all* of this set of tags? That feels like the sort of thing that it should be possible to write in a single query, but I keep coming back to an initial query, followed by some processing in code, with a follow up query. I'm sure there's a more sql-ish way. Chris _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users