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 ?

score 34 · Accepted Answer · edited May 23 '17 at 12:25

34

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)

edited May 23 '17 at 12:25

Community

1
1

answered Jan 12 '12 at 10:40

Jon Black

16,223
5
43
42

@f00, so are they ever going to implement it? Or is the answer "never"? – Pacerier Jan 20 '15 at 04:29

score 4 · Answer 2 · edited Aug 04 '17 at 19:14

4

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

edited Aug 04 '17 at 19:14

Kuba hasn't forgotten Monica

95,931
16
151
313

answered Aug 04 '17 at 16:59

Hubbitus

5,161
3
41
47

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/16938206) – Jonathan Aug 04 '17 at 18:58
@Jonathan But it does answer the question :) – Kuba hasn't forgotten Monica Aug 04 '17 at 19:12
1

@KubaOber You're right - I probably should have marked it has link-only answer instead. You should really add some code to explain how this works rather than just linking to a website. This way it's less likely to get flagged and down-voted. Once you have done that, leave a comment and I will retract my comment. – Jonathan Aug 04 '17 at 19:15
In fact I expect it should now work "as is". If there some other problems in run that query on MySQL then question should be clarified with details. – Hubbitus Aug 04 '17 at 19:54

score 0 · Answer 3 · answered Jul 11 '18 at 03:31

0

unfortunately MYSQl or XAMPP(MARIADB) mysql doesnot support CTEs(COMMON TABLE EXPRESSIONS), for the same you will have to use nested queries.

for more information click on the below link:-

https://mariadb.com/kb/en/library/with/

answered Jul 11 '18 at 03:31

NupzNupz

1
3

1

Please include essential information from the linked site in your answer - links are not so dependable. – CertainPerformance Jul 11 '18 at 03:49

score 0 · Answer 4 · answered Feb 12 '21 at 13:06

Please check what version of MySQL you have using SELECT VERSION(); If it is 8 or above you can then proceed further with my comment. With MySQL 8.0, MariaDB 10.2, and later versions, you can use recursive CTEs:

WITH RECURSIVE 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 ;

Note that CTEs are limited by cte_max_recursion_depth (default 1000, max 4,294,967,295 (2³²−1)) in MySQL and by max_recursive_iterations (default 4,294,967,295) in MariaDB.

You can increase the limit by executing:

SET cte_max_recursion_depth = 4294967295;

It will only affect your current session and won't be persisted.

How to transform a MSSQL CTE query to MySQL?

4 Answers4

Linked

Related