Recursive Dynamic Query

Question

I have this recursive SQL query where I obtain the hierarchy of IDs of every office (an office belongs to higher rank office and such) - inspired by @leftclickben's answer in How to do the Recursive SELECT query in MySQL?:

select @pv := o.office_id, o.display_name, (
                        select concat(concat(group_concat(@pv := t.parent_office_id order by t.parent_office_id asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office order by (CASE WHEN parent_office_id < office_id THEN parent_office_id  END) DESC,
                                            (CASE WHEN parent_office_id > office_id THEN parent_office_id  END) ASC) t 
                                                where t.office_id = @pv 
                                            ) 'hierarchy'
from office o
group by o.office_id
order by o.office_id asc, o.parent_office_id desc
;

For this query to work, it needs perferct descendance or ascendance to be verified. This property is not verified in my data, and thus I had to take advantage of selective order by in the subquery.

I obtained very encouraging results (80% accuracy), and I was wondering if anyone could suggest more techniques to obtain better results?

Examples:

- for some office (ID = 97), its hierarchy is 1.2.4.14.97 (accurate value);

- for another case (ID = 101), I get: 111.101 (broken hierarchy);

In a nutshell, all results must start with 1.

A good sample:

http://sqlfiddle.com/#!9/82f13/1

If you filter to where the hierarchy list starts with 1 do you have the proper results? — Hart CO, Sep 19 '15 at 21:26
In this case, it's a backward hierarchy. So when I start with 1, I should get 1 (actual result is NULL), If I start with 2, I should get 1.2 and so on ... — Mohamed Ennahdi El Idrissi, Sep 19 '15 at 21:32
What I mean is, does your result set contain all the correct hierarchies in addition to the broken ones? If so, you can filter out the broken ones and be done with it. — Hart CO, Sep 19 '15 at 21:55
@MohamedEnnahdiElIdrissi Sorry your samples doesnt match your fiddle, Values 97 and 101 doesnt exists — Juan Carlos Oropeza, Sep 20 '15 at 00:04

Mohamed Ennahdi El Idrissi · Accepted Answer · 2015-09-20T23:13:21.453

Based on @Hart CO's suggestion, I solved this with three queries with two unions:

(select @pv := o.office_id, o.display_name, ifnull((
                        select concat(concat(group_concat(@pv := t.parent_office_id order by t.parent_office_id asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office order by parent_office_id desc) t 
                                                where t.office_id = @pv 
                                            ), '1.') 'hierarchy'
from office o
where office_id not in (26, 27, 28, 29, 30, 32, 33, 34, 41, 57, 58, 59, 60, 61, 62, 63, 64, 73, 74, 75, 76, 77, 79, 82, 91, 96, 101, 102, 103, 104)
group by o.office_id
order by o.parent_office_id desc)
 union
(

select @pv := o.office_id, o.display_name, (
                        select concat(concat(group_concat(@pv := t.parent_office_id order by t.parent_office_id asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office order by parent_office_id  DESC) t 
                                                where t.office_id = @pv 
                                            ) 'hierarchy'
from office o
where office_id in (26, 27, 28, 29, 30, 32, 33, 34, 41, 57, 58, 59, 60, 61, 62, 63, 64, 91, 96, 101, 102, 103, 104)
group by o.office_id
order by o.parent_office_id desc
)
union
(select @pv := o.office_id, o.display_name, (
                        select concat(concat(group_concat(@pv := t.parent_office_id order by (select parent_office_id from office where office_id = t.parent_office_id)  asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office oo order by (select parent_office_id from office where office_id = oo.parent_office_id) deSC) t 
                                                where t.office_id = @pv 
                                            ) 'hierarchy'
from office o
where office_id in (73, 74, 75, 76, 77, 79, 82)
group by o.office_id
 order by o.parent_office_id desc
);

Indeed, the first query was straight forward: parent_id is smaller.

For the second query, the parent_id was larger at the bottom level.

For the third query, the parent_id of the parent is larger, that's why I opted for a sub-query in the order by block for both group_concat and the sub-query with the alias t.

I can finally move forward with my ETL.

Recursive Dynamic Query

1 Answers1