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
