How to transform a MSSQL CTE query to MySQL?

Question

in my MySQL schema, I have the category(id, parentid, name) table

In the MSSQL, I have that CTE query (to build a category tree from the bottom up for a supplied category ID:

with CTE (id, pid, name)  as (     select id, parentid as pid,name     from category     where id = 197       union all         select CTE.pid as id , category.parentid as pid, category.name         from CTE            inner join category              on category.id = CTE.pid  )  select * from CTE  

How to 'transform' that query to MySQL ?

Answer 1

Unfortunately MySQL doesn't support CTE (Common Table Expressions). This is long overdue IMO. Often, you can just use a subquery instead, but this particular CTE is recursive: it refers to itself inside the query. Recursive CTE's are extremely useful for hierarchical data, but again: MySql doesn't support them at all. You have to implement a stored procedure to get the same results.

A previous answer of mine should provide a good starting point:

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

Answer 2

Thankfully it's not necessary anymore, as MySQL starting from 8.0.1 supports CTE.