I have an implementation of the nested set model where I build a tree-menu for a website. On some occations, I need to be able to hide a menu-item, and replace it with its children. So if I have the following information in the db


sportID spName Visible Left Right
-----------------------------------------------------
1 Markets 0 1 12
2 Soccer 1 2 9
3 Ice Hockey 1 10 11
5 England 1 3 4
6 Sweden 1 5 6
7 Norway 1 7 8

If I use the following query to select all elements with indentation:

SELECT COUNT(P2.spName)-2 AS indentation, P1.sportID, P1.spName, P1.lft, P1.rgt, P1.visible
FROM bxSport P1 CROSS JOIN
bxSport P2
WHERE (P1.lft BETWEEN P2.lft AND P2.rgt) AND P1.visible = 1 AND P1.lft <> 1
GROUP BY P1.spName, P1.lft, P1.rgt, P1.visible, P1.sportID
ORDER BY P1.lft

This gives the following result (first column is indentation):

indent ID Name Left Right Visible
-------------------------------------------------------
0 2 Soccer 2 13 1
1 5 England 3 4 1
1 6 Sweden 5 6 1
1 7 Norway 7 12 1
2 8 Tippeligaen 8 9 1
2 9 Cup 10 11 1
0 3 Ice Hockey 14 15 1


The query only shows elements with visible set to 1, and doesn't include the root node in the result.

If I want to hide element soccer (set visible to 0), All subelements of Soccer (England, Sweden, Norway, Tippeligaen and Cup) should have its indent decreased with one, and result in the table

//0 2 Soccer 2 13 0 (hidden, and should not be in the result)
0 5 England 3 4 1 (indent decreased by one)
0 6 Sweden 5 6 1 (indent decreased by one)
0 7 Norway 7 12 1 (indent decreased by one)
1 8 Tippeligaen 8 9 1 (indent decreased by one)
1 9 Cup 10 11 1 (indent decreased by one)
0 3 Ice Hockey 14 15 1

Also, if I decide to hide Norway too, Tippeligaen and Cup should be displayed on the same level as England and Sweden (which again should be displayed on the same level as Ice hockey).

How can I modify the query so hidden elements are not part of the result, and subelements of these elements are placed where its parent would be?


Thanks in advance

Shai`tan