0

I have this scenario with this table:

CREATE TABLE `elements` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `ref_element_id` bigint(20) UNSIGNED NOT NULL,
  `ref_element_id_parent` bigint(20) UNSIGNED DEFAULT NULL,
  `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL
);

which contains some elements like:

ID    ref_element_id   ref_element_id_parent    name
171 , 211388375      , 211388376              , 'element_10'
158 , 211388376      , 211388243              , 'element_9'
131 , 211388243      , 211388242              , 'element_8'
149 , 211388242      , 211388241              , 'element_7'
150 , 211388241      , 211388240              , 'element_6'
153 , 211388240      , 211388239              , 'element_5'
146 , 211388239      , 211388238              , 'element_4'
136 , 211388238      , 211388237              , 'element_3'

where parent of element_10 is element_9 and so on... Actually I used to run a recursive query with with recursive clause on a database like :

SELECT 
    A.ref_element_id, 
    A.ref_element_id_parent, 
    A.name, 
    A.level 
    FROM ( 
        with recursive parent_users (ref_element_id, ref_element_id_parent, name, level) AS (
        SELECT ref_element_id, ref_element_id_parent, name, 1 level
        FROM elements
        WHERE ref_element_id = 211388375
        union all
        SELECT t.ref_element_id, t.ref_element_id_parent, t.name, level + 1
        FROM elements t INNER JOIN parent_elements pu
        ON t.ref_element_id = pu.ref_element_id_parent
    )
    SELECT * FROM parent_elements ) A LIMIT 10

that would return to me this:

ID    ref_element_id   ref_element_id_parent    name           level
171 , 211388375      , 211388376              , 'element_10',  1
158 , 211388376      , 211388243              , 'element_9',   2
131 , 211388243      , 211388242              , 'element_8',   3
149 , 211388242      , 211388241              , 'element_7',   4
150 , 211388241      , 211388240              , 'element_6',   5
153 , 211388240      , 211388239              , 'element_5',   6
146 , 211388239      , 211388238              , 'element_4',   7
136 , 211388238      , 211388237              , 'element_3',   8

but since I have moved this table on a mysql 5.7 db which doesn't support this convenient with recursive clause.... how I could modify that query to obtain the same result?

Thanks to all in advance! Cheers!

Luigino
  • 545
  • 2
  • 5
  • 21
  • Does this answer your question? [How do you use the "WITH" clause in MySQL?](https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql) (The accepted answer shows how to do it in 5.7) – Luuk Mar 09 '22 at 11:47

0 Answers0