Discussion :

[PHCM64310]

Bonjour

je cherche a faire une requête recursive, j explique
j ai une table individu, chaque individu a (en principe un père (CLE_PERE) et une mere (CLE_MERE)) donc en partant de moi CAZAUX Philippe (ou d un autre individu) je voudrais avoir les individus par ordre de generation avec aussi une colonne qui donne la génération

Merci de votre aide

[PHCM64310]

J ai bien trouvé ceci https://www.sqlite.org/lang_with.html

mais je n y comprends

[vanagreg]

Bonjour,

Le principe d'une requête récursive se base sur une clause WITH avec un UNION ALL:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
WITH v(liste_colonnes)
AS (select liste_colonnes
      from t
      where --> point de départ
      union all
      select liste_colonnes
      from v
      join ... on
      where --> condition d'arrêt
     )

Le "haut" du bloc représente le point de départ. Ici par exemple ce serait cle_fiche=2, mais tu peux très bien avoir plusieurs points de départ, si tu veux par exemple la généalogie de plusieurs personnes.
Le "bas" du bloc représente la récursivité, c'est elle qui permet de boucler, et c'est pour ça que tu retrouves le nom de la vue with déclarée au début. En gros tu vas joindre "v" sur individu sur chaque cle_pere et cle_mere. Tu t'arrêtes quand ces derniers son null.

Pourrais-tu fournir un jeu de données (par exemple les inserts) pour que l'on puisse tester?

Merci

[PHCM64310]

Vanagreg merci

ai essayé d adapter, ai pas réussi

voici script de ma petite base

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "INDIVIDU" (
	"CLE_FICHE"	INTEGER NOT NULL UNIQUE,
	"KLE_DOSSIER"	Integer,
	"CLE_PERE"	Integer,
	"CLE_MERE"	Integer,
	"PREFIXE"	Varchar(30),
	"NOM"	Varchar(40) NOT NULL,
	"PRENOM"	Varchar(60),
	"SURNOM"	Varchar(120),
	"SUFFIXE"	Varchar(30),
	"SEXE"	Integer,
	"DATE_NAISSANCE"	Date,
	"ANNEE_NAISSANCE"	Integer,
	"DATE_DECES"	Date,
	"ANNEE_DECES"	Integer,
	"AGE_AU_DECES"	Integer,
	"SOURCE"	Blob,
	"COMMENT"	Blob,
	"NUM_SOSA"	Double precision,
	"NCHI"	Smallint,
	"NMR"	Smallint,
	"IND_CONFIDENTIEL"	Smallint,
	"DATE_CREATION"	Timestamp,
	"CREE_PAR_QUI"	Varchar(30),
	"DATE_MODIF"	Timestamp,
	"MODIF_PAR_QUI"	Varchar(30),
	"INDI_PHOTO"	Blob,
	PRIMARY KEY("CLE_FICHE" AUTOINCREMENT)
);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (1,NULL,3,4,NULL,'CAZAUX','Michel',NULL,NULL,NULL,'15/08/1947',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (2,NULL,3,4,NULL,'CAZAUX','Philippe',NULL,NULL,NULL,'02/08/1949',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'');
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (3,NULL,7,NULL,NULL,'CAZAUX','Gerard',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (4,NULL,9,10,NULL,'PUIBARAUD','Monique',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (5,NULL,NULL,4,NULL,'LOOS','SYLVIE',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (6,NULL,2,8,NULL,'CAZAUX','Kevin',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (7,NULL,NULL,NULL,NULL,'CAZAUX','Alexandre',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (8,NULL,NULL,NULL,NULL,'JACQUES','Francoise',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (9,NULL,11,12,NULL,'PUIBARAUD','Edgard',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (10,NULL,NULL,NULL,NULL,'ARTHAPIGNET','Marie',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (11,NULL,NULL,NULL,NULL,'PUIBARAUD','Hipolytte',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (12,NULL,NULL,NULL,NULL,'ABADIE','Julia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
CREATE INDEX IF NOT EXISTS "NOM_INDIVIDU" ON "INDIVIDU" (
	"NOM"
);
CREATE VIEW LISTE_INDI_PARENTS AS
select 
       t.nom as nom_individu,
       t.prenom as prenom_individu,
	   t.date_naissance as ne_individu,
       t_pere.nom as nom_pere,
       t_pere.prenom as prenom_pere,
       t_mere.nom as nom_mere,
       t_mere.prenom as prenom_mere
from INDIVIDU t
left join INDIVIDU t_pere on t_pere.cle_fiche = t.cle_pere
left join INDIVIDU t_mere on t_mere.cle_fiche = t.cle_mere;
COMMIT;

[PHCM64310]

sur la table pas sur la vue qui est pour autre chose

[SQLpro]

Voir l'article que j'ai écrit à ce sujet : https://sqlpro.developpez.com/cours/...te-recursives/

A +

[PHCM64310]

J ai essayé d adapter la requête de Fréderic

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH tree (data, id, level, pathstr)                        
AS (SELECT VHC_NAME, VHC_ID, 0,
           CAST('' AS VARCHAR(MAX))           
    FROM   T_VEHICULE                       
    WHERE  VHC_ID_FATHER IS NULL          
    UNION ALL                               
    SELECT VHC_NAME, VHC_ID, t.level + 1, t.pathstr + V.VHC_NAME
    FROM   T_VEHICULE V                     
           INNER JOIN tree t 
                 ON t.id = V.VHC_ID_FATHER)
SELECT SPACE(level) + data as data, id, level, pathstr
FROM   tree
ORDER  BY pathstr, id

Pas réussi

[vanagreg]

La difficulté ici c'est qu'il y a en fait 2 clauses récursives à faire, l'une pour les ascendants masculins, l'autre pour les ascendants féminins. Voici donc la requête en commençant par exemple par cle_fiche = 2:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
with v1(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
    from individu i
    join individu pere on pere.cle_fiche = i.cle_pere 
    join individu mere on mere.cle_fiche = i.cle_mere 
    where i.cle_fiche = 2
    union all 
    select pere.cle_fiche, pere.nom, pere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
    from v1
    join individu pere on pere.cle_fiche = v1.cle_pere 
    left join individu mere on mere.cle_fiche = pere.cle_mere
    ) ,
v2(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
    from individu i
    join individu pere on pere.cle_fiche = i.cle_pere 
    join individu mere on mere.cle_fiche = i.cle_mere 
    where i.cle_fiche = 2
    union all 
    select mere.cle_fiche, mere.nom, mere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
    from v2 
    join individu mere on mere.cle_fiche = v2.cle_mere
    left join individu pere on pere.cle_fiche = V2.cle_pere
    )     
select case when v1.nom is null then v2.nom else v1.nom end nom, 
       case when v1.prenom is null then v2.prenom else v1.prenom end prenom, 
       case when v1.niveau > 1 then lead(v1.nom_ascendant_masculin) over (order by v1.niveau) else v1.nom_ascendant_masculin end nom_ascendant_masculin,
       case when v1.niveau > 1 then lead(v1.prenom_ascendant_masculin) over (order by v1.niveau) else v1.prenom_ascendant_masculin end prenom_ascendant_masculin,
       case when v2.niveau > 1 then lead(v2.nom_ascendant_feminin) over (order by v2.niveau) else v2.nom_ascendant_feminin end nom_ascendant_feminin,
       case when v2.niveau > 1 then lead(v2.prenom_ascendant_feminin) over (order by v2.niveau) else v2.prenom_ascendant_feminin end prenom_ascendant_feminin,
       case when v1.niveau is null then v2.niveau else v1.niveau end niveau
from v1
full join v2 on v2.cle_fiche = v1.cle_fiche
order by niveau;

[PHCM64310]

Vanagreg, merci bcp pour ton travail mais chez moi ca ne passe pas

L'exécution s'est terminée avec des erreurs.
Résultat : RIGHT and FULL OUTER JOINs are not currently supported

[PHCM64310]

ai essayé de comprendre, en fait c est le mot full devant le join de la fin qui est refusé, si je l enlevé et laisse juste join la requête est accepté mais je n ai plus qu un seul enregistrement si je mets LEFT JOIN la ai 3 enregistrements

FULL n est pas accepté : https://www.sqlitetutorial.net/sqlite-full-outer-join/

[vanagreg]

Bonjour,

Ok le full outer join n'est pas supporté par SQLlite. Il faut l'émluler avec des left join et un union all, comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
v1_full_join_v2
as (select 1 ascendant,
           v1.cle_fiche,
           v1.nom,
           v1.prenom,
           v1.nom_ascendant_masculin,
           v1.prenom_ascendant_masculin,
           v2.nom_ascendant_feminin,
           v2.prenom_ascendant_feminin,
           v1.niveau
    from v1
    left join v2 on v1.cle_fiche = v2.cle_fiche
    union all
    select 2 ascendant,
           v2.cle_fiche,
           v2.nom,
           v2.prenom,
           v1.nom_ascendant_masculin,
           v1.prenom_ascendant_masculin,
           v2.nom_ascendant_feminin,
           v2.prenom_ascendant_feminin,
           v2.niveau
    from v2
    left join v1 on v1.cle_fiche = v2.cle_fiche
    where v1.cle_fiche is null
   )

ce qui donne pour requête globale:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
with v1(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
    from individu i
    join individu pere on pere.cle_fiche = i.cle_pere 
    join individu mere on mere.cle_fiche = i.cle_mere 
    where i.cle_fiche = 2
    union all 
    select pere.cle_fiche, pere.nom, pere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
    from v1
    join individu pere on pere.cle_fiche = v1.cle_pere 
    left join individu mere on mere.cle_fiche = pere.cle_mere
    ) ,
v2(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
    from individu i
    join individu pere on pere.cle_fiche = i.cle_pere 
    join individu mere on mere.cle_fiche = i.cle_mere 
    where i.cle_fiche = 2
    union all 
    select mere.cle_fiche, mere.nom, mere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
    from v2 
    join individu mere on mere.cle_fiche = v2.cle_mere
    left join individu pere on pere.cle_fiche = V2.cle_pere
    ),
v1_full_join_v2
as (select 1 ascendant,
           v1.cle_fiche,
           v1.nom,
           v1.prenom,
           v1.nom_ascendant_masculin,
           v1.prenom_ascendant_masculin,
           v2.nom_ascendant_feminin,
           v2.prenom_ascendant_feminin,
           v1.niveau
    from v1
    left join v2 on v1.cle_fiche = v2.cle_fiche
    union all
    select 2 ascendant,
           v2.cle_fiche,
           v2.nom,
           v2.prenom,
           v1.nom_ascendant_masculin,
           v1.prenom_ascendant_masculin,
           v2.nom_ascendant_feminin,
           v2.prenom_ascendant_feminin,
           v2.niveau
    from v2
    left join v1 on v1.cle_fiche = v2.cle_fiche
    where v1.cle_fiche is null
   )    
 
select nom, 
       prenom, 
       case when niveau > 1 then lead(nom_ascendant_masculin) over (partition by ascendant order by niveau) else nom_ascendant_masculin end nom_ascendant_masculin,
       case when niveau > 1 then lead(prenom_ascendant_masculin) over (partition by ascendant order by niveau) else prenom_ascendant_masculin end prenom_ascendant_masculin,
       case when niveau > 1 then lead(nom_ascendant_feminin) over (partition by ascendant order by niveau) else nom_ascendant_feminin end nom_ascendant_feminin,
       case when niveau > 1 then lead(prenom_ascendant_feminin) over (partition by ascendant order by niveau) else prenom_ascendant_feminin end prenom_ascendant_feminin,
       niveau
from v1_full_join_v2
order by niveau;

[PHCM64310]

Merci encore cette fois ci elle est acceptée mais le résultat est incomplet je devrais avoir

Niveau	Nom
1	CAZAUX Philippe
2	CAZAUX Gerad
2	PUIBARAUD Monique
3	CAZAUX Alexandre
3	PUIBARAUD Edgard
3	ARTHAPIGNET Marie
4	PUIBARAUD Hippolyte
4	ABADIE Juila

merci à toi

[vanagreg]

Tu as raison. Je me suis rendu compte que j'avais compliqué inutilement la requête. En fait une seule requête récursive est nécessaire (j'ai changé la condition de jointure). Comme on ne peut pas accéder au parent directement, j'utilise la fonction analytique LEAD qui permet d'aller chercher un ligne par rapport à un offset. Enfin, pour avoir le père et la mère sur la même ligne, je pivote à l'aide d'un MAX(CASE WHEN...END). Voici la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
with v(cle_fiche, nom, prenom, genre, cle_fiche_parent, nom_parent, prenom_parent, cle_pere, cle_mere, niveau)
as (select i.cle_fiche, i.nom, i.prenom, case when p.cle_fiche = i.cle_pere then 1 else 2 end genre, p.cle_fiche cle_fiche_parent, p.nom nom_parent, p.prenom prenom_parent, i.cle_pere, i.cle_mere, 1 
    from individu i
    join individu p on p.cle_fiche in (i.cle_pere, i.cle_mere) 
    where i.cle_fiche = 2
    union all
    select i.cle_fiche, i.nom, i.prenom, case when i.cle_fiche = v.cle_pere then 1 else 2 end, i.cle_fiche, i.nom, i.prenom, i.cle_pere, i.cle_mere, 1 + niveau 
    from v
    join individu i on i.cle_fiche in (v.cle_pere, v.cle_mere)
   ),   
v_dedoublonnee 
as (select distinct 
       nom, 
       prenom,
       genre,
       nom_parent,
       prenom_parent,
       niveau
    from v 
   ),
v_parents 
as (select nom, 
           prenom,
           genre,
           case when niveau > 1 then lead(nom_parent) over (partition by genre order by niveau) else nom_parent end nom_parent,
           case when niveau > 1 then lead(prenom_parent) over (partition by genre order by niveau) else prenom_parent end prenom_parent,
           niveau
    from v_dedoublonnee
   )
select niveau,
       nom,
       prenom,
       max(case when genre = 1 then nom_parent end) nom_pere,
       max(case when genre = 1 then prenom_parent end) prenom_pere,
       max(case when genre = 2 then nom_parent end) nom_mere,
       max(case when genre = 2 then prenom_parent end) prenom_mere
from  v_parents
group by niveau,
         nom,
         prenom
order by niveau;

[PHCM64310]

C est nickel
vraiment merci pour ton travail

[bolduc2]

bonjour

j'arrive un peu tard mais je propose une deuxième solution:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
WITH RECURSIVE
  ancetre(niveau,nom2,prenom2,cle_fiche2,cle_pere2,cle_mere2) AS (
      SELECT 1,nom, prenom,cle_fiche,cle_pere,cle_mere 
      FROM individu
      WHERE cle_fiche=2
    UNION 
      SELECT niveau+1,nom,prenom,cle_fiche,cle_pere,cle_mere 
      FROM individu,ancetre
      WHERE cle_fiche IN (cle_pere2,cle_mere2)   
  )
SELECT niveau,nom2,prenom2 FROM ancetre ORDER BY niveau;

[PHCM64310]

Bolduc
merci elle marche nickel aussi