Query parent and child entries of one element in a single row

Question

Hi there,

I'm searching for the best way to display parent and child entries in one row. Example:

Table A
 ID   |  PARENT_ID   |  VALUE
=============================
 1    |              | A
 2    |      1       | B
 3    |      2       | C
 4    |              | D
 5    |      4       | E

So I want to get the following result:

 ID   |  PARENT      | CHILD     |  VALUE
 =========================================
 2    |    1         |   3       |  A
 5    |    4         |           |  E

How would you solve this?

Any help his very appreciated.

Daniel

Answer 1

Hi hkda150,

You can do it easily with a self-join:

SQL> WITH table_a AS (
  2   SELECT 1 ID, NULL parent_id, 'A' VALUE FROM dual
  3   UNION ALL SELECT 2, 1, 'B' FROM dual
  4   UNION ALL SELECT 3, 2, 'C' FROM dual
  5   UNION ALL SELECT 4, NULL, 'D' FROM dual
  6   UNION ALL SELECT 5, 4, 'E' FROM dual
  7  )
  8  SELECT children.id, children.parent_id,
  9         grand_children.id, children.value
 10    FROM (SELECT ID, parent_id, VALUE
 11             FROM table_a
 12            WHERE LEVEL = 2
 13           CONNECT BY parent_id = PRIOR ID
 14            START WITH parent_id IS NULL) children
 15    LEFT JOIN table_a grand_children
 16           ON children.id = grand_children.parent_id;

        ID  PARENT_ID         ID VALUE
---------- ---------- ---------- -----
         2          1          3 B
         5          4            E