SQLite User Forum

Help with a search command
Login
Having fields with a serial number in their name suggests that your schema is not normalized, either by omission or by design. Since this is a home-use project...

There appear to be 2 "entities" in your application: "Diary entries" and "Keywords", so lets put them into separate tables:

CREATE TABLE entry (id INTEGER PRIMAY KEY, content TEXT);
CREATE TABLE keyword (id INTEGER PRIMARY KEY, word TEXT COLLATE NOCASE UNIQUE);

The "diary entries" and the "keywords" have an N:M relationship, meaning that each "diary entry" may have 0 or more kewords, and each keyword my refer to 0 or more "diary entries". This goes into another table

CREATE TABLE entry_keyword (entry_id INTEGER, key_id INTEGER, UNIQUE(entry_id, key_id));

Your query then becomes a join:

SELECT e.text FROM keyword k JOIN entry_keyword ek ON (k.id = ek.key_id) JOIN entry e ON (e.id = ek.entry_id) WHERE k.word LIKE '%<text>%';

You can look into foreign keys, referential integrity actions, and ON CONFLICT clauses later.