How can I show a name using the ID as the foreign key?
Here's what I have:
I have a table called Area that is self referential. If the column ParentAreaID is null it means it's a parent area. If it has a value开发者_JAVA百科, it means that it has a parents.
How can I show a table with ID, Name and NAME (of the parents using the self referential ID)?
You want to use connect by: http://psoug.org/reference/connectby.html
create table sample (id number, parentid number, name varchar2(50));
insert into sample values(1,null,'aaa');
insert into sample values(2,NULL,'bbb');
insert into sample values(3,NULL,'ccc');
insert into sample values(4,null,'ddd');
insert into sample values(5,1,'aaa1');
insert into sample values(6,2,'bbb2');
insert into sample values(7,3,'ccc3');
insert into sample values(8,4,'ddd4');
insert into sample values(9,5,'aaa11');
insert into sample values(10,6,'bbb22');
insert into sample values(11,7,'ccc33');
insert into sample values(12,8,'ddd44');
SELECT ID,PARENTID, NAME, CONNECT_BY_ROOT NAME PARENTNAME,
SYS_CONNECT_BY_PATH(name, '/')
FROM SAMPLE
start with parentid is null
CONNECT BY PRIOR ID = PARENTID
order siblings by id;
精彩评论