Hierarchical query in MySQL

Question

Having that table structure & data:

| ID | PARENT | FIELD_1 | FIELD_2 | RATING |
+------------------------------------------+
|  1 | NULL   | F1V1    | F2V1    | 10     |
|  2 | 1      | F1V2    | F2V2    | 20     |
|  3 | 2      | F1V3    | F2V3    | 30     |
|  4 | 3      | F1V4    | F2V4    | 40     |

Is there a way of getting a result like this one:

| ID | F_1  | F_2  | P_F_1  | P_F_2  | G_F_1  | G_F_2  | S_R  | 
+-------------------------------------------------------------+
|  1 | F1V1 | F2V1 | NULL   | NULL   | NULL   | NULL   | 10   |
|  2 | F1V2 | F2V2 | F1V1   | F2V1   | NULL   | NULL   | 30   |
|  3 | F1V3 | F2V3 | F1V2   | F2V2   | F1V1   | F2V1   | 60   |
|  4 | F1V4 | F2V4 | F1V3   | F2V3   | F1V2   | F2V2   | 90   |

What I actually want, as you can see, is for every record if there are parent (P), grandparent (G), etc records (the recursion may go for 4 levels or any other finite number that is already known), the fields of their ancestors prefixed (that can happen programmatically outside of the query) and a SUM (or any other GROUP function) that calculates the values recursively as well.

ex record #4:

ID = 4
FIELD_1 AS F_1 = F1V4
FIELD_2 AS F_2 = F2V4
PARENT_FIELD_1 AS P_F_1 = F1V3 
...
GRANDPARENT_FIELD_2 AS G_F_2 = F2V2
SUM_RATING AS S_M = (40 + 30 + 20) = 90**

NOTE: Even though record #1 is an ancestor of record #4 (grand-grandparent) its rating is not calculated in the sum because it is not contained in the query.

Do `FIELD_1` and `FIELD_2` share the same parent? This isn't clear to me. — Tim Biegeleisen, Apr 04 '16 at 11:51
@TimBiegeleisen FIELD_1 and FIELD_2 are two fields of any record. What I want is beside the current record fields to get as aliases those fields as aliases from their ancestors. — Chris Athanasiadis, Apr 04 '16 at 12:04

score 1 · Accepted Answer · answered Apr 04 '16 at 12:18

1

This simplest way:

    SELECT t.id,
           t.field_1 f_1,
           t.field_2 f_2,
           p.field_1 p_f_1,
           p.field_2 p_f_2,
           g.field_1 g_f_1,
           g.field_2 g_f_2,
           t.rating + COALESCE(p.rating,0) + COALESCE(g.rating,0) s_r
     FROM table_name t
LEFT JOIN table_name p
       ON p.id = t.parent
LEFT JOIN table_name g
       ON g.id = p.parent

And add left joins, additions and field selections to the known level of recursion.

answered Apr 04 '16 at 12:18

Arth

12,789
5
37
69

Yes that's the solution. I wrote a solution based on this answer: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query but because I used the joins in opposite direction (ancestor.parent = descendant.id) I didn't get the expected result. – Chris Athanasiadis Apr 04 '16 at 12:52

Hierarchical query in MySQL

1 Answers1