How to transform a MSSQL CTE query to MySQL?

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

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)