Yes, but the problem is that I need to also retrieve the articles themselves. If I were to embed the articles query inside the staff query (as you’ve shown), the database would have to execute the article query twice.
> On Mar 27, 2019, at 4:42 PM, Keith Medcalf <[email protected]> wrote: > > > 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 _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
