Oracle SQL full hierarchy given any node as input

Question

I would like to create a full hierarcy search query in Oracle SQL database. I have a following table, called "item".

id   name  parent_id
1    A     NULL
2    B     1
3    C     2
4    D     3
5    E     2

Input is any of a given id from id column. It should find all of this id's children, their children. As well parents and their parents. At the moment I have following query:

select distinct m.id, m.parent_id
    from item m
connect by prior m.id = m.parent_id
    start with m.parent_id IN (
        select m.parent_id
        from item m
        connect by m.id = prior m.parent_id
    start with m.id = 3
    union
    select m.parent_id
        from item m
    where m.parent_id = 3);

At the moment it seems to be working only so that parent which does not have parent (parent_id column is null) is not selected. Otherwise it seems to be working. Also if my given query could be simplified I would appreciate it as well.

EDIT

I think I got the desired result with following query below:

select m.id
    from item m
    start with m.id in (
        select m.id
        from item m
        where connect_by_isleaf = 1
        start with m.id = 3
        connect by m.id = prior m.parent_id
    )
    connect by m.parent_id = prior m.id;

Now there is next issue I have. start with m.id = 3. Issue is that I would like to create a view out of this whole query. But as m.id value changes from query to query I cannot add it as a parameter. There's also a possibility to comment out start with m.id = 3 and then it would return all hierarchies between all items. Is there a way to create some join? E.g.: I would query all those relations of all items and then by some condition get only certain item relations.

Answer 1

If you want to use it as view you can do something as the following:

WITH rek AS (SELECT item.id
                  , item.name
                  , connect_by_root item.id root_id
               FROM item
              START WITH parent_id IS null
            CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
     , startItem.name startName
     , childItem.id childID
     , childItem.name childName
  FROM rek startItem
  JOIN rek childItem
  USING (root_id)
-- WHERE startItem.id = 3 -- This would be done from outside the view

The subquery rek connects all sibling of the tree with the root element. Then you only have to use this query twice and connect it via the root element to get all elements that are connected via parent-child relation.

If you want to reduce the resultset you can use the SYS_CONNECT_BY_PATH to do so:

WITH rek AS (SELECT item.id
                  , item.name
                  , connect_by_root item.id root_id
                  , SYS_CONNECT_BY_PATH(item.id, '/') path
               FROM item
              START WITH parent_id IS null
            CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
     , startItem.name startName
     , childItem.id childID
     , childItem.name childName
     , childItem.path 
  FROM rek startItem
  JOIN rek childItem
    ON startItem.root_id = childItem.root_id
    AND (startItem.path LIKE childItem.path||'/%'
      OR childItem.path LIKE startItem.path||'/%'
      OR childItem.id = startItem.id)

This for example will give you only the childs and parents of you starting point and no entries from other leafs.

Answer 2

Oracle Setup:

CREATE TABLE item ( id, name, parent_id ) AS
  SELECT 1, 'A', NULL FROM DUAL UNION ALL
  SELECT 2, 'B',    1 FROM DUAL UNION ALL
  SELECT 3, 'C',    2 FROM DUAL UNION ALL
  SELECT 4, 'D',    3 FROM DUAL UNION ALL
  SELECT 5, 'E',    2 FROM DUAL;

You can get the item and all its ancestors using:

SELECT *
FROM   item
START WITH id = 2
CONNECT BY PRIOR parent_id = id

and you can get all the descendants of the item using:

SELECT *
FROM   item
START WITH parent_id = 2
CONNECT BY PRIOR id = parent_id

And you can combine the two using UNION ALL. However, this puts the matched item first then ancestors in order of increasing ancestry then descendants in order of decreasing ancestry... which might be confusing.

So you can reorder the query to put both in a consistent ordering:

SELECT *
FROM   (
  SELECT *
  FROM   item
  START WITH id = 2
  CONNECT BY PRIOR parent_id = id
  ORDER BY LEVEL DESC
)
UNION ALL
SELECT *
FROM   (
  SELECT *
  FROM   item
  START WITH parent_id = 2
  CONNECT BY PRIOR id = parent_id
  ORDER SIBLINGS BY name
);

Output:

ID NAME PARENT_ID
-- ---- ---------
1  A    -
2  B    1
3  C    2
4  D    3
5  E    2