Person_id,Mapped_person_id
For Example
Person_ID Mapped_Person_id
1 2
2 3
3 4
5 6
6 7
I require a query which will give output like below
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7
Scripts
Create table Person (person_id Number, Mapped_person_id Number);
INSERT INTO PERSON VALUES ( 1,2);
INSERT INTO PERSON VALUES ( 2,3);
INSERT INTO PERSON VALUES ( 3,4);
INSERT INTO PERSON VALUES ( 5,6);
INSERT INTO PERSON VALUES ( 6,7);
Solution:
SELECT level, person_id AS original_person_id,
CONNECT_BY_ROOT person_id AS root_person_id, mapped_person_id
FROM person
CONNECT BY person_id = prior mapped_person_id;
No comments:
Post a Comment