Mysql – Single query for retrieving a category path

MySQLrecursive

I have the following table structure and content:

+-------------+-----------+------------+
| category_id | parent_id |    name    |
+-------------+-----------+------------+
|          1  |        0  | Test 1     |
|          2  |        1  | Test 1.1   |
|          3  |        2  | Test 1.1.1 |
|          4  |        0  | Test 2     |
|          5  |        0  | Test 3     |
|          6  |        5  | Test 3.1   |
|          7  |        6  | Test 3.1.1 |
+-------------+-----------+------------+

Is it possible to write a single query that will return the results as following:

+-------------+--------------------------------+
| category_id |              path              |
+-------------+--------------------------------+
|           1 | Test 1                         |
|           2 | Test 1 > Test 1.1              |
|           3 | Test 1 > Test 1.1 > Test 1.1.1 |
|           4 | Test 2                         |
|           5 | Test 3                         |
|           6 | Test 3 > Test 3.1              |
|           7 | Test 3 > Test 3.1 > Test 3.1.1 |
+-------------+--------------------------------+

Thank you!

Best Answer

Assuming that the values in the name column are consistent with the parent_id values (and the resulting tree), you can use a triangular join and GROUP_CONCAT():

SELECT 
    t.category_id,
    GROUP_CONCAT(anc.name ORDER BY anc.name
                 SEPARATOR ' > ') AS path
FROM tablename AS t                         -- for every row in t
  JOIN tablename AS anc                     -- find its ancestors
    ON t.name LIKE CONCAT(anc.name, '%')
GROUP BY
    t.category_id ;

Test in SQLfiddle.