hello lists,
i want to use the nested set model to store a tree structure in a database.
the normal way i do this looks like this:
My table looks something like this:
ID LEFT RIGHT NAME
1 1 14 Root
2 2 7 Thema 1
3 3 4 Thema 1.1
4 8 13 Thema 2
5 5 6 Thema 1.2
6 11 12 Thema 2.2
7 9 10 Thema 2.1
Now I get a sorted tree Structure with the following select:
SELECT *, count(*) AS level
FROM tree AS a, tree AS b
WHERE b.l BETWEEN a.l AND a.r AND
b.r BETWEEN a.l AND a.r
GROUP BY b.id ORDER BY b.l
The result looks like this:
ID LEFT RIGHT LEVEL NAME
1 1 14 1 Root
2 2 7 2 Thema 1
3 3 4 3 Thema 1.1
5 5 6 3 Thema 1.2
4 8 13 2 Thema 2
...
But know i want to use more than one tree in a table. This looks like this:
ID LEFT RIGHT TREE NAME
1 1 6 1 Thema 1
2 2 3 1 Thema 1.1
3 2 5 4 Thema 2.1
4 1 6 4 Thema 2
5 3 4 4 Thema 2.1.1
6 4 5 1 Thema 1.2
Now i use the following select:
SELECT *, count(*) AS level
FROM tree AS a, tree AS b
WHERE b.l BETWEEN a.l AND a.r AND
b.r BETWEEN a.l AND a.r AND
a.id = b.tree
GROUP BY b.id ORDER BY b.tree,b.l
The Output looks like the output above. The problem ist, that
the level wouldnt be counted. the result of level is alway 1.
what can i do to count the right level of each tree ?
can anybody help ?
regards,
tobias.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
