Version 11g
Hi Gurus
I 'm practicing some stuff but unable to make one query, see below for sample data:
Sample data
create table ancestry (
id integer primary key
, name varchar2(10) not null
, father integer references ancestry
)
/
insert into ancestry values ( 1, 'AAdam', null)
/
insert into ancestry values ( 10, 'BBill', 1)
/
insert into ancestry values ( 20, 'BBuck', 1)
/
insert into ancestry values ( 110, 'CCarl', 10)
/
insert into ancestry values ( 120, 'CChip', 10)
/
insert into ancestry values ( 210, 'CCole', 20)
/
insert into ancestry values (1210, 'DDale', 110)
/
insert into ancestry values (2110, 'DDick', 210)
/
commit
/
Required Output
ID, NAME, FATHER GRAND_FATHER
1 AAdam
10 BBill 1
20 BBuck 1
110 CCarl 10 AAdam
120 CChip 10 AAdam
210 CCole 20 AAdam
1210 DDale 110 BBill
2110 DDick 210 BBuck
I'm trying to display Grand father but I couldn't.
Rules:
Get father for current record and then go to that and the get the father of that record and print name.
In summary need to find the grand father name.