Getting all first-level children of "opened" items in nested set model

Question

So I have a tree structure implemented as nested set. I also have a TreeView control which displays the said tree structure.

Initially all elements are "closed" meaning I only see the first-level items. Now the user can open / close items at will and I keep track of opened items. All opened items are inserted into the TreeView.

I retrieve them using this statement:

  select opened.tt_id, tree_test.*
    from tree_test as opened
    join tree_test on tree_test.tt_nsleft between opened.tt_nsleft + 1 and opened.tt_nsright
     and not exists (select * from tree_test t1 where t1.tt_nsleft between opened.tt_nsleft + 1 and opened.tt_nsright and tree_test.tt_nsleft between t1.tt_nsleft + 1 and t1.tt_nsright - 1)
   where opened.tt_id in (1, 3, 8)
order by tt_nsleft

The user can drilll down at will opening 3-rd or 4th level items. And this is where I run into an issue: if the user has some 3rd, 4th, etc. level items open and he now closes their 1st level parent, the above query will still return all those sub-items.

What is the best way to "validate" the opened items such that only those would remain that are direct children of actually visible items?

I have a solution which I think is quite wasteful and it simply checks the opened items against the result set:

  select opened.tt_id, tree_test.*
    from tree_test as opened
    join tree_test on tree_test.tt_nsleft between opened.tt_nsleft + 1 and opened.tt_nsright
     and not exists (select * from tree_test t1 where t1.tt_nsleft between opened.tt_nsleft + 1 and opened.tt_nsright and tree_test.tt_nsleft between t1.tt_nsleft + 1 and t1.tt_nsright - 1)
   where opened.tt_id in (1, 3, 8)
     and opened.tt_id in (
          select tree_test.tt_id
            from tree_test as opened
            join tree_test on tree_test.tt_nsleft between opened.tt_nsleft + case when opened.tt_nsleft = 1 then 0 else 1 end and opened.tt_nsright
             and not exists (select * from tree_test t1 where t1.tt_nsleft between opened.tt_nsleft + 1 and opened.tt_nsright and tree_test.tt_nsleft between t1.tt_nsleft + 1 and t1.tt_nsright - 1)
           where opened.tt_id in (1, 3, 8)
     )
order by tt_nsleft

This works because the result set contains only first-level children of all requested items. If an item is not in the result set, then it must have originated from a level further below. This of course means double work for the server as it actually has to gather the data twice in order to be able to filter out the invalid opened items.

The alternate solution would be to search for each opened item's parent and check it against list of opened items (+ null for top level item). However, the query for that doesn't seem much lighter.

Is there a third option I'm missing here?

Answer 1

After much experimenting (and failing) I have found a solution that is just as elegant as any basic nested set operation:

select tree_test".*
  from tree_test
 where not exists (
        select *
          from tree_test parents
         where tree_test.tt_nsleft between (parents.tt_nsleft + 1) and parents.tt_nsright
       and not (parents.tt_id in (12384898975268895, 12384898975268897, 12384898975268902))
   )

Effectively, this query says: "select all items where there is no parent whose id is not among those listed". Effectively, the condition for specifying "opened" items is arbitrary, it doesn't have to be item IDs.