-- Recherche des ascendants
\i data_ascendants.sql

/*
         Blandine 
                  \   Zoee
Clara            /        \  Julia
       \    Paul           /       \
       /              Yvan         /  Adrien
Jean                             /
     \   Claire                /
     /         \    Louis    /
Nadia          / 
         Zoile
*/

WITH RECURSIVE ancetre(Aieul, Enfant) AS 
	(SELECT Pere, Enfant FROM Parents
	UNION 
	SELECT Mere, Enfant FROM Parents
	UNION  
	SELECT A.Aieul, P.Enfant 
			FROM 	Parents AS P, 
				ancetre AS A 
				WHERE P.Pere =A.Enfant OR  P.Mere =A.Enfant )
SELECT * FROM ancetre where Enfant='Julia';
/*
   aieul    |   enfant   
------------+------------
 Yvan       | Julia     
 Zoee       | Julia     
 Paul       | Julia     
 Blandine   | Julia     
 Claire     | Julia     
 Clara      | Julia     
 Jean       | Julia     
 Nadia      | Julia     
(8 rows)
*/

DROP VIEW IF EXISTS  ascendant;
CREATE View ascendant(ascendant,enfant) AS (
WITH RECURSIVE ancetre(Aieul, Enfant) AS 
	(SELECT Pere, Enfant FROM Parents
	UNION 
	SELECT Mere, Enfant FROM Parents
	UNION  
	SELECT A.Aieul, P.Enfant 
			FROM 	Parents AS P, 
				ancetre AS A 
				WHERE P.Pere =A.Enfant OR  P.Mere =A.Enfant )
SELECT * FROM ancetre);


SELECT * FROM ascendant where Enfant='Julia';
/*
 ascendant  |   enfant   
------------+------------
 Yvan       | Julia     
 Zoee       | Julia     
 Paul       | Julia     
 Blandine   | Julia     
 Claire     | Julia     
 Clara      | Julia     
 Jean       | Julia     
 Nadia      | Julia     
(8 rows)


*/
