We help IT Professionals succeed at work.

Relationship Query in SQL.

there is orders table where there is one column
ID
1
2
3
4
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
1
1
1
2
2
2
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

How can I do that?
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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.

Author

Commented:
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
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
is it always 3? meaning for one id you will have 3 cid?
HainKurtSr. System Analyst
CERTIFIED EXPERT

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

Author

Commented:
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) "
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
first lest say

Q = select po.id, co.cid, rank() over (partition by po.id order by co.cid) as r from parentorder po, childorder co where po.id=co.id

now,

select  id,
case when r=1 then cid else 0 end as c1,
case when r=2then cid else 0 end as c2,
...
case when r=n then cid else 0 end as cn
from Q

say this is Q1

now we need to group this or merge this

select id, (max(c1) || ',' || max(c2) || ... || max(cn)) as C  
from Q1
group by id
order by id

:)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Can you post some "real" data for the two tables and what you expect the output to be?
Information Technology Specialist
CERTIFIED EXPERT
Commented:
You can create a function and execute it as per attached.
function.txt

Author

Commented:
Thanks.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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: http://www.experts-exchange.com/Database/Oracle/Q_22433572.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
/

Open in new window

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
You're welcome.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.