Hi Durante muchos días he estado trabajando en este problema en MySQL, sin embargo, no puedo resolverlo. ¿Alguno de ustedes tiene sugerencias?

Básicamente, tengo una tabla de categorías con dominios como:id, name (nombre de la categoría) yparent (id del padre de la categoría).

Datos de ejemplo:

1  Fruit        0
2  Apple        1
3  pear         1
4  FujiApple    2
5  AusApple     2
6  SydneyAPPLE  5
....

Hay muchos niveles, posiblemente más de 3 niveles. Quiero crear una consulta sql que agrupe los datos según la jerarquía: padre> hijo> nieto> etc.

Debería generar la estructura de árbol de la siguiente manera:

1 Fruit 0
 ^ 2 Apple 1
   ^ 4 FujiApple 2
   - 5 AusApple 2
     ^ 6 SydneyApple 5
 - 3 pear 1

¿Puedo hacer esto usando una sola consulta SQL? La alternativa, que probé y funciona, es la siguiente:

SELECT * FROM category WHERE parent=0

Después de esto, vuelvo a recorrer los datos y selecciono las filas donde parent = id. Esto parece una mala solución. Debido a que es mySQL, los CTE no se pueden usar.