[SOLVED] Select across multiple tables

Select across multiple tables

I have three SQLite tables, created with the following script

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS entries(
    timestamp TEXT,
    title TEXT,
    body TEXT
);

CREATE TABLE IF NOT EXISTS tags(
    tag TEXT
);

CREATE TABLE IF NOT EXISTS entry_tags(
    entry_id INTEGER;
    tag_id INTEGER,
    FOREIGN KEY (entry_id) REFERENCES entries(rowid),
    FOREIGN KEY (tag_id) REFERENCES tags(rowid)
);

My question is simple, but I cannot work out how to answer it. How do I use SQL to get an entry's timestamp, title, body and all associated tags? I'm assuming it involves a JOIN somewhere in the process, but despite searching, I cannot find an answer that I can understand.

Solution

Yes, you want to do a JOIN, using your entry_tags junction table. Something like this (note I am just using the Sqlite command line tool, not the Python module, but the SQL commands are the same):

sqlite> select e.timestamp, e.title, e.body, t.tag 
   ...> from entries as e 
   ...>      join entry_tags et on e.rowid = et.entry_id
   ...>      join tags as t on et.tag_id = t.rowid;
┌────────────┬───────────┬────────────────┬───────┐
│ timestamp  │   title   │      body      │  tag  │
├────────────┼───────────┼────────────────┼───────┤
│ 2024-11-01 │ Article A │ blah blah blah │ alpha │
│ 2024-11-01 │ Article A │ blah blah blah │ beta  │
└────────────┴───────────┴────────────────┴───────┘

However, note that you will get duplicate rows for an article if there is more than one tag on it. In this case, you can use the group_concat function (see String Aggregation in sqlite) to aggregate the tags (separated by a comma by default):

sqlite> select e.timestamp, e.title, e.body, group_concat(t.tag) as tags 
   ...> from entries as e 
   ...>      join entry_tags et on e.rowid = et.entry_id
   ...>      join tags as t on et.tag_id = t.rowid;
┌────────────┬───────────┬────────────────┬────────────┐
│ timestamp  │   title   │      body      │    tags    │
├────────────┼───────────┼────────────────┼────────────┤
│ 2024-11-01 │ Article A │ blah blah blah │ alpha,beta │
└────────────┴───────────┴────────────────┴────────────┘

As an aside, please note that Sqlite will not generally let you use the implicit rowid column as a primary key (and thus reference as a foreign key) unless you explicitly put it in the table schema (see https://www.sqlite.org/rowidtable.html). For example, you should define entries and tags as follows:

CREATE TABLE entries(
    rowid INTEGER PRIMARY KEY,
    timestamp TEXT,
    title TEXT,
    body TEXT
);
CREATE TABLE tags(
    rowid INTEGER PRIMARY KEY,
    tag TEXT
);