SQL & PL/SQL

Hi guys,

a have a problem that I think it can be solved whit hierarchical queries.

the question is like this. I have a person ...and each person has a mother and a father. (the mother/father may be or not be in my table)

so my table configuration will be like:

create table person
(id number,
name varchar2 (100),
mother_id number,
father_id number
)

the questions I need to answer are like:

1. return all persons that have all grandparents (or grand grand parents) in this table.(including parents). so entire genealogic tree is intact
e.g. if the grandfather is not in the table I will not display that person we cannot display that person






I have something like:

insert into person values (1,'alin', 4, 5); ----id 1 has parents in my table: 4 the mother, 5 the father

insert into person values (4,'mother_1', 8,null); ----id 4 has only her mother in table
insert into person values (5, 'father_1',9,10);--id 5(who is father of 1) with 9 as mother and 10 as father
insert into person values (8, 'grant',null ,null );
insert into person values (9, 'aaa',11,12);
insert into person values (10,'bbb',13,14);
insert into person values (11,'ccc',null ,null);
insert into person values (12, 'ddd',null ,null);
insert into person values (13,'eee',15,16);
insert into person values (14,'fff',17,18);
insert into person values (15,'ggg',null ,null);
insert into person values (16,'hhh',null ,null);
insert into person values (17,'iii',null ,null);
insert into person values (18,'jjjj',null,null);

in this configuration my select should return id 5 with his relatives and id 10 with this relatives because only them have all relatives in table:

id         |         name      |          mother    |      father      |      mather_mother (mm)   |        father_mother  (fm)  |      mf    |        ff    
5          |         'father'    |           9           |      10      |              11            |            12              |     13     |      14
10        |          bbb        |           13         |      14      |                15            |             16              |       17   |        18

and second select ..if I input an id..lets say 1 to get the same result..like:

id        |          name         |       mother     |     father   |         mather_mother (mm) |         father_mother  (fm) |       mf   |         ff    
1         |         'alin'           |      4              |   5           |             8           |       null           |      9       |     10

and maybe at some point I want to show level 3(with grand grand parents)

please se the attached picture:

link: [http://picasaweb.google.com/alinbor/Tree#5428860555382426514]


Thanks