How to pull parents in a hierarchical table design

Question

I'm working on a database with a category tree that's hierarchical. I'd like to be able to be able to write a query that returns all of the parents. For example, assume this structure/content. A parent of 0 means that it's a root element, no parents.

ID | Name     | Parent
1  | Tools    | 0
2  | Drill    | 1
3  | Impact   | 2
4  | Cordless | 2
5  | Series X | 4

How could I write a query that would get all of the parents of Series x (ID 5)? I don't care if it's inclusive of ID 5, since I would already have that one. I'd like to see it return the below results.

ID | Name     | Parent
1  | Tools    | 0
2  | Drill    | 1
4  | Cordless | 2
5  | Series X | 4

Bonus if there's a way to find how many generations they are at the same time. Something like:

ID | Name     | Parent | Generation
1  | Tools    | 0      | 0
2  | Drill    | 1      | 1
4  | Cordless | 2      | 2
5  | Series X | 4      | 3

I'm really stuck on this right now. I am thinking it might need to be a custom sql function?

Answer 1

In MySQL 8.0, they now support recursive CTE queries:

WITH RECURSIVE cte AS (
  SELECT * FROM MyTable WHERE id = 5
  UNION ALL
  SELECT MyTable.* FROM cte JOIN MyTable WHERE MyTable.id = cte.parent
)
SELECT * FROM cte ORDER BY id;

Getting the "Generation" when your CTE starts at the leaf of the hierarchy is tricky.

If you are using a version of MySQL older than 8.0, you may like my answer to What is the most efficient/elegant way to parse a flat table into a tree? or my presentation Recursive Query Throwdown.