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.