convert cte to mysql

Question

i have a recursive cte in mssql which finds all children of a certain node. in db terms:

create table nodes (
     id         int primary key autoincrement,
     parent     int
)

with this table i had a cte that i used to create a view:

create view (
     node_id    int,
     child_id   int
)

how do i do that in mysql? (I cannot change the table format to accomodate other methods such as with the nested set model)

worst case, could i make a function to do it?

thanks!

To convert a normal `cte` just make it into a view but I assume your `cte` was recursive? In which case there is no support in mysql for these. See [this answer](http://stackoverflow.com/questions/3276136/recursive-self-query/3276806#3276806) for some useful links on the subject of representing hierarchical data in MySQL. — Martin Smith, Dec 07 '10 at 11:25
possible duplicate of [How do you use the "WITH" clause in MySQL?](http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql) — Ben, Nov 15 '13 at 13:22

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

You may want to look at this discussion:

edited May 23 '17 at 10:33

Community

answered Jan 06 '11 at 06:24

Chris Pietschmann

1 Answers1