SQLite: how to concatenate JOIN

Question

I have three tables in my database, related to physical exercises:

exercises(id, name)

tags(id, name)

exercises_tags(id_exercise, id_tag)

The exercises_tags table stores which tags corresponds to which exercise. One exercise can have multiple tags.

Example:

exercises(id, name):
(1, 'squat')

tags(id, name)
(1, 'legs')
(2, 'quads')
(3, triceps)

exercises_tags(id_exercise, id_tag)
(1, 1)
(1, 2)

I want a query that, for a given exercise name, it returns the corresponding tag names, in the example input: squat; output = ['legs', 'quads'] . I know how to get the exercise id in a query and the corresponding id_tag in another query, but I don't know how to put all together and get the final result. I think I have to use JOIN, but I don't know how to perform two JOINs.

Answer 1

If you join all 3 tables you will get all the rows of tags that are related to the input exercise.
Python's fetchall() will return these rows as a list of sublists.
Each sublist will contain only 1 item and by extracting all these items in a for loop you can get the result as a list:

db = 'database_name.db'

con = sqlite3.connect(db)
cur = con.cursor()

sql = """
    SELECT t.name
    FROM exercises AS e
    INNER JOIN exercises_tags AS et ON et.id_exercise = e.id
    INNER JOIN tags AS t ON t.id = et.id_tag
    WHERE e.name = ?;
"""
list = [item[0] for item in cur.execute(sql, ('squat', )).fetchall()]

print(list)

It will print:

['legs', 'quads']