1

I am trying to build one SQL query that returns both all parents and all children.

This first SQL returns all parents given a base

    SELECT BASE,
         LISTAGG (PARENT_ENTITY_ID, ';') WITHIN GROUP (ORDER BY BASE)
            AS PARENTS
    FROM (    SELECT CONNECT_BY_ROOT CHILD_ENTITY_ID BASE,
                     CHILD_ENTITY_ID,
                     PARENT_ENTITY_ID
                FROM P360_RCT_ENTTY_RLTNSHP
          CONNECT BY PRIOR PARENT_ENTITY_ID = CHILD_ENTITY_ID)
GROUP BY BASE

This second SQL returns all children given the base

SELECT BASE,
         LISTAGG (CHILD_ENTITY_ID, ';') WITHIN GROUP (ORDER BY BASE)
            AS CHILDREN
    FROM (    SELECT CONNECT_BY_ROOT PARENT_ENTITY_ID BASE,
                     CHILD_ENTITY_ID,
                     PARENT_ENTITY_ID
                FROM P360_RCT_ENTTY_RLTNSHP
          CONNECT BY PRIOR CHILD_ENTITY_ID=PARENT_ENTITY_ID)
GROUP BY BASE

Is there any way to return both of them in one single SQL query where I pass the base and I get a list of children and parents? I searched on SO and found several questions related to the topic but not specifically on getting both parents and children in one query.

Erick
  • 823
  • 16
  • 37

0 Answers0