Return parent row then child rows in Oracle SQL query

Question

I'm trying to return data from family in the following structure:

• Parent A row
• A's child 1 row
• A's child 2 row
• A's child 3 row
• Parent B row
• B's child 1 row
• B's child 2 row
• B's child 3 row

Family returns parent and child data all together, like:

• 1 parent A columns, A's child 1 columns
• 2 parents A columns, A's child 2 columns
• 3 parents A columns, A's child 3 columns

My attempts so far with connect by prior results in the error:

ORA-01436: CONNECT BY loop in user data

I suspect, but I'm not certain, that should be using connect by level instead, but each of my attempts with that fails.

What am I doing wrong? How can I achieve my desired format?

with parents
as (
    select *
    from view_sot sot
    where sot.tree = 'parent'
        and sot.xmode = 'E'
    ),
children
as (
    select *
    from view_sot sot
    where sot.tree = 'child'
        and sot.xmode != 'E'
    ),
family
as (
    select parents.srn psrn,
        parents.cand pcand,
        parents.ayrc payrc,
        parents.mod_code pmod_code,
        parents.mark pmark,
        parents.grade pgrade,
        parents.xmode pxmode,
        parents.mavo pmavo,
        parents.mod_name pmod_name,
        parents.prg_code pprg_code,
        parents.tree ptree,
        children.srn csrn,
        children.cand ccand,
        children.ayrc cayrc,
        children.mod_code cmod_code,
        children.mark cmark,
        children.grade cgrade,
        children.xmode cxmode,
        children.mavo cmavo,
        children.mod_name cmod_name,
        children.prg_code cprg_code,
        children.tree ctree
    from parents
    inner join children on parents.srn = children.srn
        and parents.mod_code = children.mod_code
        and parents.ayrc = children.ayrc
        and parents.cand = children.cand
        and parents.mavo = children.mavo
    where parents.srn = 'A012345678'
    )
select psrn,
    pxmode,
    pmod_code,
    cmod_code,
    cxmode,
    level
from family connect by prior cmod_code = pmod_code

Many thanks

Answer 1

Without any context or data in your question, I just built a silly example - is this what you want?

with family as
  (select 1 as id_num, 'Jay-Z' as the_name, 'parent' as who, 2 as family, 0 as parent_id from dual
    union all
   select 2 as id_num, 'Beyonce' as the_name, 'parent' as who, 2 as family,     0 as parent_id from dual
     union all
   select 11 as id_num, 'Blue Ivy' as the_name, 'child' as who, 2 as family,     1 as parent_id from dual
      union all
   select 11 as id_num, 'Blue Ivy' as the_name, 'child' as who, 2 as family,     2 as parent_id from dual
   union all
    select 22 as id_num, 'Upcoming Twin #1' as the_name, 'child' as who, 2     as family, 1 as parent_id from dual
   union all
    select 22 as id_num, 'Upcoming Twin #1' as the_name, 'child' as who, 2     as family, 2 as parent_id from dual
       union all
     select 33 as id_num, 'Upcoming Twin #2' as the_name, 'child' as who, 2     as family, 1 as parent_id from dual
   union all
    select 33 as id_num, 'Upcoming Twin #2' as the_name, 'child' as who, 2     as family, 2 as parent_id from dual)

select lpad('.',level*3-3,'.')||the_name
from family
where ((level = 1 and who = 'parent') or (level = 2))
connect by prior id_num = parent_id
order siblings by the_name;   



OUTPUT >>>>
Beyonce
...Blue Ivy
...Upcoming Twin #1
...Upcoming Twin #2
Jay-Z
...Blue Ivy
...Upcoming Twin #1
...Upcoming Twin #2

I'm unclear as to if you have multiple rows per child (one row per child per parent).... but hopefully this syntax will at least guide you.