On Wed, Mar 5, 2014, at 09:59 AM, Chris wrote: > 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
Having realised the above sounds very much like "please do my homework assignment for me", I should point out that that's not the case. I've been a C, C++ and Tcl developer for many years, but have only ever scratched the surface of sqlite. This is me trying to delve a little deeper while avoiding going down too many wrong turns at the beginning. Chris _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users