can I search two sqlite fts5 index's across two tables?

198 views Asked by At

I have a tasks application with two tables. One table has the task name, date, owner etc and the other has the comments for the task linked to the task number so there can be multiple comments attached to a single task.

Both tables have FTS5 indexes. Within my app I want to search both tables for a word and present the rows to the user. I have the below working for each table individually but how do I construct a query that returns data from both FTS5 tables?

(python3.6)

c.execute("select * from task_list where task_list = ? ", [new_search])
c.execute("select * from comments where comments = ? ", [new_search])

thanks @tomalak never thought of doing that, was focused on the query. Here's what I came up with and works for my purposes. Probably better ways to achieve the same result but I'm a beginner. This is a Tkinter app.

def db_search():
rows = ''
conn = sqlite3.connect('task_list_database.db')
c = conn.cursor()
d = conn.cursor()
new_search = entry7.get()
c.execute("select * from task_list where task_list = ? ", [new_search])
d.execute("select * from comments where comments = ? ", [new_search])
rows1 = c.fetchall() 
rows2 = d.fetchall()
rows = rows1 + rows2
clear_tree(tree)   
for row in rows:
    tree.insert("", END, values=row)        
conn.close()
0

There are 0 answers