Hey MySql Pros hope you can get me a hint, I've a problem with my NestedSets Structure.
I have two NestedSets Structures, the first Structure(MainNestedSets) holds my nested sets information and a ReferenceCID to another NestedSets Structure(OtherNestedSets) see below.
Now I want to receive the Result set seen in ResultAfterQuery, how can I solve this problem with only one query?
Have I to implement a procedure to handle this or had some one a solution for my problem.
Looking forward to your answers,
Cheers Nils
Description of the Database Structure
The MainNestedSets hold my Information an has a ReferenceCID that point on the OtherNestedSets.
MainNestedSets
CategoryID | Name | Lft | Rgt | ReferenceCID
1 | Category1 | 1 | 10 |
2 | Category2 | 2 | 3 |10
3 | Category3 | 4 | 5 |
4 | Category4 | 6 | 7 |
5 | Category5 | 8 | 9 |
OtherNestedSets
CategoryID | Name | Lft | Rgt |
10 | OtherCat1 | 1 | 10 |
20 | OtherCat2 | 2 | 5 |
30 | OtherCat3 | 3 | 4 |
40 | OtherCat4 | 6 | 7 |
50 | OtherCat5 | 8 | 9 |
ResultAfterQuery
This is the ResultSet that I want to generate with one query.
CategoryID | Name | Lft | Rgt | level
1 | Category1 | 1 | 10 | 1
2 | Category2 | 2 | 3 | 1
10 | OtherCat1 | 1 | 10 | 2
20 | OtherCat2 | 2 | 5 | 2
30 | OtherCat3 | 3 | 4 | 3
40 | OtherCat4 | 6 | 7 | 2
50 | OtherCat5 | 8 | 9 | 2
3 | Category3 | 4 | 5 | 1
4 | Category4 | 6 | 7 | 1
5 | Category5 | 8 | 9 | 1