Recursive query mysql

Question

I am not able to get recursive data with a query that uses this statement:

WITH RECURSIVE

I'm trying to get the rows related on the table:

id |      name     |extern|
--------------------------|
1  |  building_01  |  null
--------------------------|
2  |    floor_01   |  1
--------------------------|
3  |    zone_04    |  2
--------------------------|
4  |    zone_05    |  3

I need to get this data: where are zone_05 ?

4 | zone_05    | 3
---------------|
3 | zone_04    | 2
---------------|
2 | floor_01   | 1
---------------|
1 | building_01| null
---------------|

I'm trying to solve it with this statement, but I only get the first related row :

select  id,
    name,
    extern
    from    (select * from table) products_sorted,
    (select @pv := '4') initialisation
    where   find_in_set(id, @pv) > 0
    and     @pv := concat(@pv, ',', extern)

Result:

4 | zone_05    | 3
---------------|
3 | zone_04    | 2
---------------|

This solution is not working for me :

How to create a MySQL hierarchical recursive query

@trincot Would it be possible to alter this to work in "reverse"? So grab all of a rows parents, grandparents, etc? I've used your first query for getting descendants but I would like to get the ancestors? as well. – shreddish Jul 19 '17 at 14:54

if anyone else was looking for the answer to the question @shreddish asked, the solution is to change on p.parent_id = cte.id to on p.id = cte.parent_id – fanfare Dec 10 '17 at 5:38

The solution is related to :

Alternative 1: WITH RECURSIVE, CONNECT BY

and I'm not able to use it because mysql database server version.

First of all add a correct layout for questions, maybe this help you to achieve some answer. And try also to add data model, to understand a little bit the problem. — mrc, Jan 30 '18 at 14:56
Here the version of mysql database server: # Variable_name, Value 'innodb_version', '1.1.8' 'protocol_version', '10' 'slave_type_conversions', '' 'version', '5.5.19-enterprise-commercial-advanced-log' 'version_comment', 'MySQL Enterprise Server - Advanced Edition (Commercial)' 'version_compile_machine', 'x86_64' 'version_compile_os', 'Linux' — Ramon González, Jan 30 '18 at 15:24
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — P.Salmon, Jan 30 '18 at 15:32
Care to explain the relevance of JPA here? You post no use of JPA API — , Jan 30 '18 at 15:54
I'm working on a app using jpa and creating named Query with Entity Manager but for this use I think there are not any solution. So I'm trying with m.createNativeQuery(sql String). No relevance. Deleted. — Ramon González, Jan 30 '18 at 16:05

user2518618 · Accepted Answer · 2018-01-31T06:39:31.997

0

This is the query that you need;

select  id,
        name,
        extern_id 
from    (select * from places
         order by id desc, extern_id desc) places_sorted,
        (select @pv := '26') initialisation
where   find_in_set(id, @pv) > 0
and     @pv := concat(@pv, ',', extern_id)

This is the fiddle: http://sqlfiddle.com/#!9/ba5700/2

As you can see, the "order by" is important as explained by trincot in the answer that you mention

edited Jan 31 '18 at 06:39

answered Jan 30 '18 at 21:28

user2518618

1,360
13
32

Take into account that this only works if id>id_extern (as trincot explains in the linked answer). In other words, "select count(*) from places where id_extern>=id" must return 0 in your DB – user2518618 Jan 31 '18 at 05:09
Ok, thank you. I will need to reorder ids from the table. – Ramon González Jan 31 '18 at 09:37

Recursive query mysql

1 Answers1