sqlsqlite

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
    );