You mean something like this: SELECT staff.* FROM staff, contributions WHERE contributions.staff = staff.email AND contributions.article IN (SELECT id FROM articles WHERE publish_date <= CURRENT_TIMESTAMP ORDER BY publish_date DESC LIMIT ?);
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >[email protected]] On Behalf Of Joshua Thomas Wise >Sent: Wednesday, 27 March, 2019 14:22 >To: SQLite mailing list >Subject: [sqlite] Feature request: dynamically referenced bind >parameters > >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 >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
