lundi 27 avril 2015

For each row in a table, count the number of times that row is referenced by another table.

I have two tables in an SQLite database, authors and threads:

CREATE TABLE IF NOT EXISTS authors(
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS threads(
    thread_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    author_id INTEGER REFERENCES authors
);

Each row in threads references the authors table. Authors can be referenced by multiple threads. I need to get the names of each author and the count of threads that reference them, sorted by the count. I imagine the output to look something like this:

author.name   Count
Jane          10
Judy          8
John          8
Jim           4

What's the query that will return this data?

Aucun commentaire:

Enregistrer un commentaire