How to link two tables in Sqlite?

Question

There are 2 tables:

CREATE TABLE note (
    note_id   TEXT PRIMARY KEY,
    note_text TEXT
);

CREATE TABLE tag (
    tag_id   TEXT PRIMARY KEY,
    tag_text TEXT
);

I want to link note and tag, like a note app.

You can set tags to a note. Also, you can search note with tag.

How to write SQL?

Answer 1

You are describing a many-to-many relationship. This suggest a third table, that references both base tables, and where each association is stored on a different row:

create table tag_notes (
    tag_id int references tags(tag_id),
    note_id int refereences notes(note_id),
    primary key (tag_id, note_id)
)

Note that I used datatype int for the foreign key columns - this is really what you should be using for the primary keys of the base tables.

With this set-up in place, you can search for all notes that are related to a given tag by text with the following query:

select n.*
from notes n
inner join tag_notes tn on tn.note_id = n.note_id
inner join tags t on t.tag_id = tn.tag_id
where t.tag_text = 'mytag'

Or with an exists condition:

select n.*
from notes n
where exists (
    select 1
    from tag_notes tn 
    inner join tags t on t.tag_id = tn.tag_id
    where t.tag_text = 'my tag' and tn.note_id = n.note_id
)