How to write a query in oracle sql?

Question

SQL

Can you please tell me how to make a request for a selection of links parent record - child?
For example, there is a table id, parent_id, name.
how to get data like:
parent_id_1 id_1 name
parent_id_1 id_2 name
....
parent_id_2 id_1 name
parent_id_2 id_2 name
...
I.e. comes the parent id and all its children, then the next parent id and its children, and so on.
Order by parent id, then by child.

Answer 1

Initial data

with t as(
  select 1 as chld_id, null as prnt_id, '1_name' as nm from dual union all
  select 3 as chld_id, 1 as prnt_id, '3_name' as nm from dual union all
  select 4 as chld_id, 1 as prnt_id, '4_name' as nm from dual union all
  select 6 as chld_id, 9 as prnt_id, '6_name' as nm from dual union all
  select 9 as chld_id, 3 as prnt_id, '9_name' as nm from dual union all
  select 12 as chld_id, 9 as prnt_id, '12_name' as nm from dual union all
  select 14 as chld_id, 9 as prnt_id, '14_name' as nm from dual union all
  select 15 as chld_id, 3 as prnt_id, '15_name' as nm from dual union all
  select 18 as chld_id, 9 as prnt_id, '18_name' as nm from dual
)

If you solve exactly the task, then you just need to change the sequence of columns and sort.
You need to join the table onto itself if the selection requires not only the ID of the parent element, but also its name.

select
 prnt_id, chld_id, nm
from t
where prnt_id is not null
order by 1,2

You can play around to see the whole hierarchy:

select 
 prnt_id
,chld_id
,level
,lpad(' ', 4*level)||nm as hierarchy_
from t
start with prnt_id is null
connect by prior chld_id = prnt_id
order by prnt_id nulls first, chld_id;

(only not on large tables in high-speed production)

Answer 2

select p.id as parent_id, c.id, c.name
from paren as p
join child as c on c.parent_id=p.id
order by p.id, c.id

Answer 3

The usual Cartesian product. Multiplying a table by itself.

select a.parent_id, a.name, b.id 
from t1 a, t1 b
order by a.parent_id, b.id;

Answer 4

Thanks for the help, I managed to display the hierarchy with the following query:

select connect_by_root ID PARENT_ID, ID
  from  dict
  connect by PARENT_ID = prior ID

All parents and all their children are displayed, but the question remains, is it possible to order the output by the moment the data was added to the selection? Those. walking around the tree.