Relationship Query in SQL.

I could not get how you got the result :) what happened to 4 in the first table?
how do you connect these two tables? did you hide the columns while posting here?

there is foreign key relationship between ID and CID. The second table also has another column ID which is a foreign key and defines the relation ship between parent and child orders.

The above data is just to clarify my question. Please ignore 4.

Parent Order table.
ID
1
2
3

and so on.

there is child orders table which is CID where there is one to many relation ship with ID in orders table
CID ID
1    1
1    1
1    1
2    2
2    2
2    2
3    3
3    3
3    3
and so on.

What I need is when I do a select on an ID in Orders table
the query should return concatenation like

ID CID
1  1,1,1
2  2,2,2
3  3,3,3

is it always 3? meaning for one id you will have 3 cid?

and I guess your post is wrong (25855358, the second table data)

no it is not always 3, one id can have any number of child id's.
I didn't understand "and I guess your post is wrong (25855358, the second table data) "

CID ID
1    1
1    1
1    1
2    2
2    2
2    2
3    3
3    3
3    3

may be you wanted to say this

CID ID
1    1
2    1
3    1
1    2
2    2
3    2
1    3
2    3
3    3

select id, (max(c1) || ',' || max(c2) || ',' || max(c3)) as C  
from (
 select  id,
case when r=1 then cid else 0 end as c1,
case when r=2 then cid else 0 end as c2,
case when r=3 then cid else 0 end as c3 from (
select po.id, co.cid, rank() over (partition by po.id order by co.cid) as r from temp_parent po, temp_child co where po.id=co.id))
group by id order by id

is working perfectly fine. However, how do I make it generic.

I don't know for sure there are n children orders for parent.

Can you post some "real" data for the two tables and what you expect the output to be?

Thanks.

You can use the function posted by awking00.  I remember a pretty slick approach that might also work for you.

I tweaked it to match your test data but the code is from: https://www.experts-exchange.com/questions/22433572/oracle-query.html

drop table tab1;
create table tab1(cid number, id number);
insert into tab1 values(1,1);
insert into tab1 values(1,1);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
insert into tab1 values(2,2);
insert into tab1 values(2,2);
insert into tab1 values(3,3);
insert into tab1 values(3,3);
insert into tab1 values(3,3);
commit; 
 
select cid, trim(',' from sys_connect_by_path(id, ',') )
from (
select cid, id,
row_number() over(partition by cid order by id) rn,
count(*) over(partition by cid) cp
from tab1
)
where rn = cp
start with rn = 1
connect by prior cid = cid
and prior rn = rn - 1
/

Select allOpen in new window

You're welcome.

lol... I swear this was still open when I posted...