Nested Set Filtering

Question

Hi,

I'm using nested set model for my menu tree, and I'm trying to get nodes with some filtering. I have several root nodes. Example:

Menu1(on)
 \-Submenu1(on)
 \-Submenu2(on)
Menu2(off)
 \-Submenu3(on)
 \-Submenu4(on)
    \-Submenu5(on)
Menu3(on)

I want to return all nodes "on" but not the ones that have parents "off". The query, for the example above, should return only Menu1 (and children) and Menu3.

Menu1(on)
 \-Submenu1(on)
 \-Submenu2(on)
Menu2(on)
 \-Submenu3(on)
 \-Submenu4(off)
    \-Submenu5(on)
Menu3(on)

For this example, the query should return all except Submenu4 and it's children.

Any ideas?

Thanks in advance.

Answer 1

SELECT t0.*
FROM sometable AS t0
LEFT JOIN sometable AS t1 ON t0.lft BETWEEN t1.lft AND t1.rgt AND t1.active='off'
WHERE t1.lft IS NULL
ORDER BY t0.lft

That is, select each row where there is no row with off that contains (or is the same row as) the target row.