I have the following 'nested set' data in a table.
| ID | name | lft | rgt | main |
|---|---|---|---|---|
| 1 | Occasion uurwerken | 1 | 28 | 1 |
| 2 | Mechanisch | 2 | 27 | |
| 3 | Alpine | 3 | 4 | |
| 4 | AS | 5 | 6 | |
| 5 | Durowe | 7 | 8 | |
| 6 | Enicar | 9 | 10 | |
| 7 | ETA | 11 | 12 | |
| 8 | FE | 13 | 14 | |
| 9 | FEF | 15 | 16 | |
| 10 | Felsa | 17 | 18 | |
| 11 | FHF | 19 | 20 | |
| 12 | Otero | 21 | 22 | |
| 13 | Peseux | 23 | 24 | |
| 14 | PUW | 25 | 26 | |
| 15 | Nieuwe uurwerken | 29 | 60 | 1 |
| 16 | Quartz | 30 | 47 | |
| 17 | Catlin | 31 | 32 | |
| 18 | Citizen | 33 | 34 | |
| 19 | ESA | 35 | 36 | |
| 20 | FE | 37 | 38 | |
| 21 | GUB | 39 | 40 | |
| 22 | Miyota | 41 | 42 | |
| 23 | Parrenin | 43 | 44 | |
| 24 | Ronda | 45 | 46 | |
| 25 | Mechanisch | 48 | 59 | |
| 26 | Enicar | 49 | 50 | |
| 27 | FE | 51 | 52 | |
| 28 | FHF | 53 | 54 | |
| 29 | Pesseux | 55 | 56 | |
| 30 | Ronda | 57 | 58 | |
| 31 | Vintage occasion | 61 | 66 | 1 |
| 32 | Certina stemvork | 62 | 63 | |
| 33 | Tissot stemvork | 64 | 65 |
I have this query to retrieve the data I need for building a menu
SELECT parent.name AS parentname, node.id, node.name AS name, node.lft, node.rgt, (
COUNT( parent.name ) -1) AS depth
FROM nested_menu AS node, nested_menu AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.id
ORDER BY node.lft";
Which works fine and returns the following data
| parentname | ID | name | lft | rgt | depth |
|---|---|---|---|---|---|
| Occasion uurwerken | 1 | Occasion uurwerken | 1 | 28 | 0 |
| Occasion uurwerken | 2 | Mechanisch | 2 | 27 | 1 |
| Occasion uurwerken | 3 | Alpine | 3 | 4 | 2 |
| Occasion uurwerken | 4 | AS | 5 | 6 | 2 |
| Occasion uurwerken | 5 | Durowe | 7 | 8 | 2 |
| Occasion uurwerken | 6 | Enicar | 9 | 10 | 2 |
| Occasion uurwerken | 7 | ETA | 11 | 12 | 2 |
| Occasion uurwerken | 8 | FE | 13 | 14 | 2 |
| Occasion uurwerken | 9 | FEF | 15 | 16 | 2 |
| Occasion uurwerken | 10 | Felsa | 17 | 18 | 2 |
| Occasion uurwerken | 11 | FHF | 19 | 20 | 2 |
| Occasion uurwerken | 12 | Otero | 21 | 22 | 2 |
| Occasion uurwerken | 13 | Peseux | 23 | 24 | 2 |
| Occasion uurwerken | 14 | PUW | 25 | 26 | 2 |
| Nieuwe uurwerken | 15 | Nieuwe uurwerken | 29 | 60 | 0 |
| Nieuwe uurwerken | 16 | Quartz | 30 | 47 | 1 |
| Nieuwe uurwerken | 17 | Catlin | 31 | 32 | 2 |
| Nieuwe uurwerken | 18 | Citizen | 33 | 34 | 2 |
| Nieuwe uurwerken | 19 | ESA | 35 | 36 | 2 |
| Nieuwe uurwerken | 20 | FE | 37 | 38 | 2 |
| Nieuwe uurwerken | 21 | GUB | 39 | 40 | 2 |
| Nieuwe uurwerken | 22 | Miyota | 41 | 42 | 2 |
| Nieuwe uurwerken | 23 | Parrenin | 43 | 44 | 2 |
| Nieuwe uurwerken | 24 | Ronda | 45 | 46 | 2 |
| Nieuwe uurwerken | 25 | Mechanisch | 48 | 59 | 1 |
| Nieuwe uurwerken | 26 | Enicar | 49 | 50 | 2 |
| Nieuwe uurwerken | 27 | FE | 51 | 52 | 2 |
| Nieuwe uurwerken | 28 | FHF | 53 | 54 | 2 |
| Pesseux | 29 | Pesseux | 55 | 56 | 2 |
| Ronda | 30 | Ronda | 57 | 58 | 2 |
| Vintage occasion | 31 | Vintage occasion | 61 | 66 | 0 |
| Certina stemvork | 32 | Certina stemvork | 62 | 63 | 1 |
| Tissot stemvork | 33 | Tissot stemvork | 64 | 65 | 1 |
Which seems fine, but in the rows with ID 29 and 30 the parent name is suddenly the same as the node name and I have no clue why?
