Oracle SQL : family tree views using trees

Question

I want to create a view who gives me the grandchildren of the oldest person who has some. And the problem is that I can't find a way to translate in sql the phrase : "who has some".

I work in only one table which is pretty basic :

Person : number(type number), lastname, firstname, dateofbirth, gender, mother(type number), father(type number).

This is what I tried to do :

I tried to create a view who gives me the oldest person who has grandchildrens (but this is not what is done here)

  CREATE OR REPLACE
  VIEW oldestone
  AS SELECT number FROM persons
     WHERE (sysdate-dateofbirth)/365 >= ALL
     (SELECT (sysdate-dateofbirth)/365 FROM persons)
     AND EXISTS (SELECT * FROM persons
                  WHERE level=3 
            START WITH number = number
            CONNECT BY PRIOR number = father OR PRIOR numero = mother);

And with the number of the first view I can get the grandchildren :

CREATE OR REPLACE
VIEW grandchildren
AS SELECT firstname,lastname FROM persons
   WHERE level=3 
   START WITH number = (SELECT number FROM oldestone)
   CONNECT BY PRIOR number = father OR PRIOR number = mother;

The problem is I know I'm not translating : the grandchildren of the oldest person who has some. Because in my first view when i wrote number = number I want to refer to the number of my select clause line 3 but i know it's not the case.

Thanks in advance for you help guys !

Chris.

Answer 1

If you reverse the direction to read "up" the family tree, rather than "down", you can go from grandchildren to their parents and then to the grandparents. As you do so, in the innermost subquery, you can "remember" the grandchildren's names with the connect_by_root() operator, and associate them with the dates of birth of their ancestors at different levels (their own dob's at level 1, their parents' dob's at level 2, their grandparents' dob's at level 3).

In the intermediate subquery I select rows produced by the hierarchical query at level 3 - that will show the dates of birth of grandparents. I use an analytic function to record the minimum date of birth in the same query. (You don't need "age" - "oldest" means earliest date of birth!)

I wrote the query as economically as possible, at each stage keeping just the columns needed to produce the required result (the names of the grandchildren of the oldest grandparents); as you select the sub-subquery and the intermediate subquery and run them separately, to understand how this works, you may want to add more columns just to see what's going on.

I created some very simple test data in a WITH clause (not part of the solution); you may want to test with more interesting inputs. Good luck!

with
     person ( id, last_name, first_name, dob, mother, father ) as (
       select  1, 'Doe', 'John', date '1990-03-02',  2,  3 from dual union all
       select  2, 'Doe', 'Anne', date '1962-11-21',  4,  5 from dual union all
       select  3, 'Doe', 'Alan', date '1960-02-23',  6,  7 from dual union all
       select  4, 'Orf', 'Jean', date '1953-10-11',  8,  9 from dual union all
       select  5, 'Orf', 'Stan', date '1952-09-06', 10, 11 from dual union all
       select 22, 'Sun', 'Ryan', date '1968-02-21', 23, 24 from dual union all
       select 23, 'Sun', 'Mary', date '1934-12-09', 26, 27 from dual
     )
--  end of test data; solution (SQL query) begins below this line
select last_name, first_name
from   (
         select last_name, first_name, dob, min(dob) over () as min_dob
         from   (
                  select connect_by_root(last_name)  as last_name ,
                         connect_by_root(first_name) as first_name, dob, level as lvl
                  from   person
                  connect by id in (prior mother, prior father)
         ) 
         where  lvl = 3
)
where  dob = min_dob
;

LAST_NAME  FIRST_NAME
---------  ----------
Doe        John

Answer 2

Table:

create table FAMILYTREE (id int, name varchar(50), MOTHERID int, FATHERID int); /

insert into FAMILYTREE (id, MOTHERID, FATHERID, name) values (1, null, null, 'My Grand Father'); insert into FAMILYTREE (id, MOTHERID, FATHERID, name) values (2, NULL, NULL, 'My Grand Mother'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (3, 10, 9, 'My Mother'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (4, 2, 1, 'My Father'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (5, 3, 4, 'Me'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (6, 12, 11, 'My wife'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (7, 3, 4, 'My Brother'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (8, 6, 5, 'My son'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (9, NULL, NULL, 'My Mother Grand Father'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (10, NULL, NULL, 'My Mother Grand Mother'); insert into FAMILYTREE (ID, MotherID, FatherID, Name) VALUES (11, null, null, 'My Wife Grand Father'); insert into FAMILYTREE (id, MOTHERID, FATHERID, name) values (12, null, null, 'My Wife Grand Mother'); commit; /

SQl :

WITH FamilyCTE(ID,name,MotherID,FATHERID,FatherName,MOTHERNAME,LVL) as ( select f.*, null as FATHERNAME,
null as MOTHERNAME, 0 as LVL from FAMILYTREE F where F.FATHERID is null AND f.MotherID IS NULL UNION ALL SELECT f.ID, f.Name AS ParentName, f.MotherID, F.FATHERID, c.Name AS FatherName, C2.name as MOTHERNAME, lvl + 1 from FAMILYTREE F INNER JOIN FamilyCTE c ON F.FatherID = c.ID INNER JOIN FamilyTree c2 ON f.MotherID = c2.ID )

select * from FAMILYCTE ;