Home » SQL & PL/SQL » SQL & PL/SQL » Recursive query
Recursive query [message #672458] Tue, 16 October 2018 04:28 Go to next message
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 #672459 is a reply to message #672458] Tue, 16 October 2018 04:31 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
Please use [code] tags as described here How to use code tags and make your code easier to read you have been asked to do this before.

What SQL have you tried so far?
Re: Recursive query [message #672461 is a reply to message #672459] Tue, 16 October 2018 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


... and can you specify with words the result you want?

Re: Recursive query [message #672466 is a reply to message #672461] Tue, 16 October 2018 05:51 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
Michel ,
Thanks for your reply.
The results is relationship between agent_id and merno.
And the agent_id is connected by parent_id.

John thank you too.
Re: Recursive query [message #672470 is a reply to message #672466] Tue, 16 October 2018 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is your Oracle version? solution depends on it.

Re: Recursive query [message #672472 is a reply to message #672470] Tue, 16 October 2018 06:10 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
Oracle 11g
Re: Recursive query [message #672473 is a reply to message #672472] Tue, 16 October 2018 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

11g is not a version, it is a commercial label.
SELECT * FROM v$version;

Re: Recursive query [message #672474 is a reply to message #672473] Tue, 16 October 2018 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
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
If this is not the case then post a test case not satisfying the assumption and give the result for it.
Re: Recursive query [message #672478 is a reply to message #672474] Tue, 16 October 2018 06:40 Go to previous message
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!
Previous Topic: Rownumer WITH OUT Order by
Next Topic: DR Failure
Goto Forum:
  


Current Time: Fri Jul 19 09:42:06 CDT 2019