MySQL getting data + recursive query

Question

I have got 3 tables:

+-----+----------+   +-----+----------+-------+  +-----+----------+-------+
| id  |   A_id   |   | A_id|   B_id   | value |  | B_id|   B_id_  | value |
+-----+----------+   +-----+----------+-------+  +-----+----------+-------+
|    1|         5|   |    5|         1|     aa|  |    1|         2|   zzxx|
+-----+----------+   +-----+----------+-------+  +-----+----------+-------+
|    2|         3|   |    3|         3|     bb|  |    2|          |   vvyy|
+-----+----------+   +-----+----------+-------+  +-----+----------+-------+
                                                 |    3|         4|   bbll|
                                                 +-----+----------+-------+
                                                 |    5|          |   oopp|
                                                 +-----+----------+-------+
                                                 |    4|         5|   mmnn|
                                                 +-----+----------+-------+

What SELECT statement i need to use, so that output would look like this(table3 can be up to 4 levels deep into it self):

+----+------------------------------+
| id |                        value |
+----+------------------------------+
|   1|                  aa\zzxx\vvyy|
+----+------------------------------+
|   2|             bb\bbll\mmnn\oopp|
+----+------------------------------+

As i don't have much experience with DB and SQL, this is hard for me. And I have no vision about how to do this.

This has to be done in MySQL. Hardest thing as i have read is the recursive query in MySQL since it doesn't exist, so people have to simulate it. I have read some SO topics about the recursive Query, but i understood that's not for me.

Any help is appreciated.

Is this some sort of reverse exercise,guess the query?Why dont YOU tell us what are the conditions for those results? — Mihai, Jul 30 '15 at 08:22
Barring punctuation, you have two columns in the same table called b_id. Don't you think this might add unnecessary complexity and confusion to the problem. — Strawberry, Jul 30 '15 at 08:34
I think i have showed it. It's collum `B_id` (foreign_key) in 2nd table and that's the primary key for 3rd table — arccuks, Jul 30 '15 at 08:35
@Strawberry It's called `B_id_` so people could understand that is the key of 3rd table, but that column is called difrently — arccuks, Jul 30 '15 at 08:37
@RyanVincent I read those articles before i asked. U see i have very little exp with DB and SQL, so after reading it I considered that's no use for me now to spent a week by learning SQL and trying to solve this problem. I am very short of free time. I didn't name tables, since i assumed that's not important u can call them table1, table2, table3 if u want. — arccuks, Jul 30 '15 at 08:57

score 0 · Accepted Answer · edited May 23 '17 at 12:01

By hard and fast learning I managed to solve my problem. Code below.

SELECT DISTINCT
    OTHER.DATA,
    concat(
        '/',ifnull(t4.value,''), CASE WHEN (t4.value is NULL) then '' else '/' END,  
        ifnull(t3.value,''), CASE WHEN (t3.value is NULL) then '' else '/' END,  
        ifnull(t2.value,''), CASE WHEN (t2.value is NULL) then '' else '/' END, 
        ifnull(t1.value,''), CASE WHEN (t1.value is NULL) then '' else '/' END, 
        table2.value
    ) as 'My Column name'

FROM
    table1

LEFT JOIN table2 ON 
    (table1.A_id = table2.A_id)

LEFT JOIN table3 as t1 ON
    (t1.B_id = table2.B_id)

LEFT JOIN table3 AS t2 ON 
    (t2.B_id = t1.B_id_) 

LEFT JOIN table3 AS t3 ON 
    (t3.B_id = t2.B_id_)

LEFT JOIN table3 AS t4 ON 
    (t4.B_id = t3.B_id_)

Big Thanks to @Damodaran and his solution for recursive query. How to create a MySQL hierarchical recursive query

Be careful with using this code, as I have used it for DB, which is only queried for data. So this approach might be slow on other different usage. If you use this, I suggest you to think about indexing some fields.

MySQL getting data + recursive query

1 Answers1