I’ve commonly encountered cases where I have a many-to-many relationship, and I would like to retrieve those relationships in a single query.
For example: CREATE TABLE staff ( email TEXT PRIMARY KEY, name TEXT ); CREATE TABLE articles ( id INTEGER PRIMARY KEY, title TEXT, body TEXT, publish_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, ); CREATE TABLE contributions( article INTEGER REFERENCES articles(id), staff TEXT REFERENCES staff(email), PRIMARY KEY(article, staff), ); First, I select the N most recently published articles: SELECT * FROM articles WHERE publish_date <= CURRENT_TIMESTAMP ORDER BY publish_date DESC LIMIT ?; Then, I’ll build a query like this to retrieve the staff that are responsible for writing those articles: SELECT staff.* FROM staff, contributions WHERE contributions.staff = staff.email AND contributions.article IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); -- assuming N was 12 in the first query However, I need to prepare a new statement every time I do this, depending on the value of N in the first query. SQLite3 already allows us to create a large number of bind parameters without explicitly declaring each one, via ?999 syntax. Now, if we had the ability to reference those bind parameters dynamically, the second query above could be something like this: WITH ids(id, n) AS ( SELECT param(1), 1 UNION ALL SELECT param(n + 1), n + 1 FROM ids WHERE n < param_count()) SELECT DISTINCT ids.id, staff.* FROM staff, contributions, ids WHERE contributions.staff = staff.email AND contributions.article = ids.id; _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users