We have a table with a self-referencing tree structure (id, parent_id). Let's assume the following tree structure:

+ 1
  + 2
  + 3
  + 4
    + 5
    + 6
    + 7
  + 8
+ 9
  + 10
  + 11

I'd like to fetch this data for displaying it in a tree. But only certain records expanded. I'm currently using the following query:

SELECT ID, NAME "PATH"
  FROM GROUPS
  WHERE PRIOR ID IN(1, 4)
  CONNECT BY PARENT_ID = PRIOR ID
  START WITH PARENT_ID IS NULL;

This works very well and returns the following records:

+ 1
  + 2
  + 3
  + 4
    + 5
    + 6
    + 7
  + 8
+ 9

The problem is that this query returns every record of which the direct parent is expanded, but not the whole parent chain. So if we're just expanding id 4, records 5, 6, 7 still shouldn't be returned as 1 is not expanded.

What I have been trying so far is to fetch a custom column which indicates whether the element is expanded, which computes out of whether it is explicitly expanded AND the parent is expanded as well.

SELECT ...
CASE WHEN (ID IN (4) AND PRIOR EXPANDED = 1) THEN 1 ELSE 0 end "EXPANDED"
...
WHERE "EXPANDED" = 1

This does not work as I can use the EXPANDED alias neither in the WHERE statement nor the PRIOR EXPANDED statement.

Is there a simple way of achieving this using a simple query?

Oracle Setup:

CREATE TABLE hierarchy ( id, parent_id ) AS
  SELECT 1, NULL FROM DUAL UNION ALL
  SELECT 2, 1 FROM DUAL UNION ALL
  SELECT 3, 2 FROM DUAL UNION ALL
  SELECT 4, 1 FROM DUAL UNION ALL
  SELECT 5, 4 FROM DUAL UNION ALL
  SELECT 6, 5 FROM DUAL UNION ALL
  SELECT 7, NULL FROM DUAL UNION ALL
  SELECT 8, 7 FROM DUAL UNION ALL
  SELECT 9, 8 FROM DUAL UNION ALL
  SELECT 10, 9 FROM DUAL UNION ALL
  SELECT 11, 8 FROM DUAL;

Query - IN clause has all parents explanded:

SELECT LPAD( '+ ', LEVEL*2, ' ' ) || id
FROM   hierarchy
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
AND        parent_id IN ( 1, 2, 4, 5, 7, 8, 9 );

Output:

+ 1
  + 2
    + 3
  +4
    + 5
      + 6
+ 7
  + 8
    + 9
      + 10
    + 11

Query - IN clause has all parents expanded except 4 and 8:

SELECT LPAD( '+ ', LEVEL*2, ' ' ) || id
FROM   hierarchy
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
AND        parent_id IN ( 1, 2, 5, 7, 9 );

Output:

+ 1
  + 2
    + 3
  +4
+ 7
  + 8

Update - Showing leaf nodes:

SELECT LPAD( '+ ', LEVEL*2, ' ' ) || id AS value,
       isleaf
FROM   (
  -- Find the leaves first (as if all parents are expanded)
  SELECT h.*,
         CONNECT_BY_ISLEAF AS isLeaf
  FROM   hierarchy h
  START WITH parent_id IS NULL
  CONNECT BY PRIOR id = parent_id
)
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
AND        parent_id IN ( 1, 2, 4, 7, 9 );

Output:

VALUE                ISLEAF
---------------- ----------
+ 1                       0
  + 2                     0
    + 3                   1
  + 4                     0
    + 5                   0
+ 7                       0
  + 8                     0

1 Indicates that the node has no children and 0 indicates that the node has children (even though they might not be expanded).