With itself, with three tables, and "those without"

A table can be joined with itself too. If every row of a category table carries the number of its parent category, then to see a category with its parent's name you put the same table into the query under two different names: one in the role of "child", one of "parent".

1SELECT c.name AS category, p.name AS parent
2FROM categories AS c
3JOIN categories AS p ON p.id = c.parent_id;

The short names are required here: the table appears twice, and only they tell which copy you mean. Those with no parent (parent_id missing) drop out of the result with a plain JOIN.

Many-to-many relations are kept in a linking table: if a book can have several tags and a tag several books, a book_tags table carries one row per pairing. Getting from book to tag takes two JOINs: book → linking table → tag. Build the chain one link at a time.

Tasks