| Recursive query [message #672458] |
Tue, 16 October 2018 04:28  |
 |
zhaoquer
Messages: 35
Registered: October 2013
|
Member |
|
|
Hi All,
Could you help me solve this probleml , thanks in advance!
create table agent_info(
agent_id varchar(20),
parent_id varchar(20)
);
insert into agent_info (AGENT_ID, PARENT_ID)
values ('wl1000', null);
insert into agent_info (AGENT_ID, PARENT_ID)
values ('wl100001', 'wl1000');
insert into agent_info (AGENT_ID, PARENT_ID)
values ('wl10000101', 'wl100001');
insert into agent_info (AGENT_ID, PARENT_ID)
values ('wl10000102', 'wl100001');
create table agent_merchant(
agent_id varchar(20),
merno varchar(20)
);
insert into agent_merchant (AGENT_ID, MERNO)
values ('wl10000101', '1000');
insert into agent_merchant (AGENT_ID, MERNO)
values ('wl10000101', '1035');
insert into agent_merchant (AGENT_ID, MERNO)
values ('wl10000102', '1100');
commit;
Expected Results:
AGENT_ID: MERNO:
wl10000102 1100
wl10000101 1000
wl10000101 1035
wl100001 1100
wl100001 1000
wl100001 1035
wl1000 1000
wl1000 1035
wl1000 1100
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Recursive query [message #672474 is a reply to message #672473] |
Tue, 16 October 2018 06:19  |
 |
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Assuming that merno is only on the leaves of the hierarchy:
SQL> with
2 data (agent_id, parent_id, merno) as (
3 select a.agent_id, a.parent_id, m.merno
4 from agent_info a, agent_merchant m
5 where a.agent_id = m.agent_id
6 union all
7 select a.agent_id, a.parent_id, d.merno
8 from data d, agent_info a
9 where a.agent_id = d.parent_id
10 )
11 select agent_id, merno
12 from data
13 order by 1 desc, 2
14 /
AGENT_ID MERNO
-------------------- --------------------
wl10000102 1100
wl10000101 1000
wl10000101 1035
wl100001 1000
wl100001 1035
wl100001 1100
wl1000 1000
wl1000 1035
wl1000 1100
|
|
|
|
| Re: Recursive query [message #672478 is a reply to message #672474] |
Tue, 16 October 2018 06:40 |
|
zhaoquer
Messages: 35
Registered: October 2013
|
Member |
|
|
Michel,
Thanks for your help!
You are right. That merno is only on the leaves.
You are so kind . Hope one day that I can give some
advice and write some codes for other people who needs
help . Learn from you!
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
