Código SQL:
Ver originalmysql> SELECT
-> REPLACE(CAST(CONCAT(M1.modelosId,
-> '.',
-> IFNULL(M2.modelosid, ''),
-> '.',
-> IFNULL(M3.modelosid, ''))
-> AS CHAR (100)), '..', '') IdModelo,
-> CONCAT(M1.modelosNombre,
-> ' ',
-> IFNULL(M2.ModelosNombre, ''), ' ',
-> IFNULL(M3.ModelosNombre, ''))nombre
-> FROM
-> modelos M1
-> LEFT JOIN
-> modelos M2 ON M1.modelosId = M2.modelosPadre
-> LEFT JOIN
-> modelos M3 ON M2.modelosId = M3.modelosPadre
-> ORDER BY M1.modelosId , M2.modelosId , M3.modelosId;
+----------+----------------------+
| IdModelo | nombre |
+----------+----------------------+
| 1.21. | BMW 320 |
| 1.53. | BMW e36 |
| 2.28. | SEAT Ibiza1 |
| 2.29. | SEAT ibiza 2 |
| 2.37. | SEAT toledo |
| 2.38. | SEAT inca |
| 3.22. | Peugeot 206 |
| 3.25. | Peugeot 207 |
| 3.26. | Peugeot 306 |
| 3.27. | Peugeot 307 |
| 3.40. | Peugeot partner |
| 6.41. | FIAT brava |
| 6.55. | FIAT bravo |
| 6.61. | FIAT stilo |
| 7.24. | volvo 123 |
| 8.30. | Ford tourneo |
| 8.34. | Ford escort |
| 8.39. | Ford focus |
| 8.50.62 | Ford mondeo 1.8g |
| 8.60. | Ford cougar |
| 9.31. | audi a3 |
| 9.51. | audi a6 |
| 10.32. | volkswagen golf |
| 10.43. | volkswagen polo |
| 11.33. | renault 19 |
| 11.44. | renault clio |
| 11.46. | renault scenic |
| 11.56. | renault 5 |
| 12.35. | hyundai accent |
| 13.36. | nissan micra |
| 13.57. | nissan trade |
| 14.42. | opel vectra |
| 15.45. | dacia logan |
| 16.48. | citroen jumper |
| 16.52. | citroen xsara |
| 17.49. | mercedes clk |
| 18.54. | chrysler voyager |
| 19.58. | toyota celica |
| 20.59. | honda accord |
| 21 | 320 |
| 22 | 206 |
| 23 | prueba |
| 24 | 123 |
| 25 | 207 |
| 26 | 306 |
| 27 | 307 |
| 28 | Ibiza1 |
| 29 | ibiza 2 |
| 30 | tourneo |
| 31 | a3 |
| 32 | golf |
| 33 | 19 |
| 34 | escort |
| 35 | accent |
| 36 | micra |
| 37 | toledo |
| 38 | inca |
| 39 | focus |
| 40 | partner |
| 41 | brava |
| 42 | vectra |
| 43 | polo |
| 44 | clio |
| 45 | logan |
| 46 | scenic |
| 48 | jumper |
| 49 | clk |
| 50.62.63 | mondeo 1.8g gasolina |
| 51 | a6 |
| 52 | xsara |
| 53 | e36 |
| 54 | voyager |
| 55 | bravo |
| 56 | 5 |
| 57 | trade |
| 58 | celica |
| 59 | accord |
| 60 | cougar |
| 61 | stilo |
| 62.63.64 | 1.8g gasolina 99 |
| 63.64. | gasolina 99 |
| 64 | 99 |
+----------+----------------------+
82 ROWS IN SET (0.02 sec)
Por cada nivel de profundidad encontrado se requiere un nuevo nivel de LEFT JOIN contra la misma tabla, y ampliar los CONCAT con los respectivos campos.
Personalmente considero que la tabla está mal diseñada, porque estás mezclando en la misma tabla tres conceptos diferentes: Marca, Modelo y DetalleModelo. Son conceptos distintos que deberían estar en tablas diferentes.
A ese esquema le falta normalización.