Suppose I have the following table
| id | category_name | parent_id |
|---|---|---|
| 1 | movies | 0 |
| 2 | technology | 0 |
| 3 | sci-fiction | 1 |
| 4 | romantic | 1 |
| 5 | documentries | 0 |
| 6 | space | 3 |
| 7 | heros | 3 |
| 8 | saturn | 6 |
| 9 | mars | 6 |
| 10 | black holes | 6 |
What I want is order these items in a way that every parent followed by his childs like this:
| id | category_name | parent_id |
|---|---|---|
| 1 | movies | 0 |
| 3 | sci-fiction | 1 |
| 6 | space | 3 |
| 8 | saturn | 6 |
| 9 | mars | 6 |
| 7 | heros | 3 |
| 4 | romantic | 1 |
| 2 | technology | 0 |
| 5 | documentries | 0 |
| 10 | black holes | 6 |
How to write a query to perform action like this?
CodePudding user response：
You can see my dbfiddle here: https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=b5c0b669f0bc611bda576502967170ee