Discussion :

[lexgotham]

Bonjour !

Je suis un cours pas à pas sur les SELECT en SQL qui introduit les :

COUNT
DISTINCT
WHERE (+ IN/EXISTS)
GROUP BY (+HAVING)
INNER/LEFT OUTER JOIN

A l'aide de ça (et de ça seulement), j'essaye d'afficher chaque petit enfant et ses 4 grands parent depuis deux tables:

1) "personnes" (id, prenom, nom)
2) "relations" (parent,enfant)

Mais je bloque.
J'arrive à afficher chaque parent et chacun de ses enfants comme ceci:

Code SQL :

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

1
2
3
4
SELECT p.nom, p.prenom, e.nom, e.prenom  
FROM personnes p
INNER JOIN relations ON p.id = relations.parent 
INNER JOIN personnes e ON e.id = relations.enfant;

Mais pour afficher les 4 grands parents de chaque petit enfant (une ligne = petit enfant + GP1+GP2+GP3+GP4), je suis dépassé par le niveau d'abstraction.

Vos lumières m'éclaireraient beaucoup.

Merci !

[lexgotham]

Bonjour,

Après quelques essais et de l'aide, j'arrive péniblement à afficher un grand parent par ligne (il faut donc 4 lignes pour afficher les 4 grand parents):

Code SQL :

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

1
2
3
4
5
6
SELECT e.nom, e.prenom, gp.nom, gp.prenom
FROM relations p_e
INNER JOIN relations gp_p ON gp_p.enfant = p_e.parent
INNER JOIN personnes gp ON gp.id = gp_p.parent
INNER JOIN personnes e ON e.id = p_e.enfant
ORDER BY e.id;

Mais je n'arrive toujours pas à trouver comment faire pour avoir à chaque ligne:

Code :

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

Grand enfant , Grand parent 1 , Grand parent 2 , Grand parent 3 , Grand parent 4

Merci.

[vttman]

Bonjour,

Alors en Access voir ceci ça devrait aider ... c'est un post de 2006 mais ça donne la direction je pense ...
=>
https://www.developpez.net/forums/d1...gnes-colonnes/

[lexgotham]

Bonjour et merci beaucoup pour ta réponse.
Je code sur sqlite3 (mais c'est vrai que les requêtes semblent similaires avec access).
Comme marqué dans ma question, je n'utilise que les join pour cet exercise, pas les pivots.
Je cherche donc une aide dans cette direction (si c'est possible).
J'ai l'impression qu'il faudrait jouer avec le GROUP BY pour y parvenir mais je cherche encore, sans succès.

[vttman]

Sans pivot ça me parait lourd mais je te donne une piste
Par contre j'ai retravaillé les alias car on s'y perd vite dans ces requêtes hiérarchiques

Code SQL :

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
SELECT e.nom, gp.nom, gp1.nom 
from  personnes e
inner join  relations r_p_e 
ON e.id = r_p_e.enfant
/* 
r_p_e:relation pere/enfant
Moi -> pere / mere */
inner join  relations r_gp_p 
ON r_p_e.parent = r_gp_p.enfant
inner join  personnes gp
on gp.id = r_gp_p.parent
/*
r_gp_p:relation grand parents/parent
pere/mere -> gp/gm */
 
inner join  relations r_gp_p1 
ON r_p_e.parent = r_gp_p1.enfant
inner join  personnes gp1
on gp1.id = r_gp_p1.parent
/* pere/mere -> gp1/gm1 */
 
where 
 gp.id <> gp1.id
/* je veux 2 id différents pour les GP */
 
limit 1
/* je prends une combinaison d'affichage au hasard  sur 4*/

[lexgotham]

Merci BEAUCOUP pour ta réponse et pour ton temps.
La difficulté de cet exercice est surtout de tous les avoir sur une ligne et de n'avoir aucun doublon de nom de petits enfant à chaque ligne.
Du genre:

Code :

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

1
2
3
4
5
6
7
8
9
Ligne 1 = Petit enfant1 / GP1 / GP2 / GP3 / GP4
Ligne 2 = Petit enfant2 / GP1 / GP2 / GP3 / GP4
Ligne 3 = Petit enfant3 / GP1 / GP2 / GP3 / GP4
Ligne 4 = Petit enfant4 / GP1 / GP2 / GP3 / GP4
Ligne 5 = Petit enfant5 / GP1 / GP2 / GP3 / GP4
Ligne 6 = Petit enfant6 / GP1 / GP2 / GP3 / GP4
Ligne 7 = Petit enfant7 / GP1 / GP2 / GP3 / GP4
Ligne 8 = Petit enfant8 / GP1 / GP2 / GP3 / GP4
etc..

Là, ton code et ce que j'obtiens de mon côté aussi ça me fait 4 lignes par petit enfant avec chaque fois un grand parent par ligne.
Du genre :

Code :

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

1
2
3
4
5
6
7
8
9
Ligne 1 = Petit enfant1 / GP1
Ligne 2 = Petit enfant1 / GP2
Ligne 3 = Petit enfant1 / GP3
Ligne 4 = Petit enfant1 / GP4
Ligne 5 = Petit enfant2 / GP1
Ligne 6 = Petit enfant2 / GP2
Ligne 7 = Petit enfant2 / GP3
Ligne 8 = Petit enfant2 / GP4
etc...

Au mieux, en fignolant ton code, j'obtiens

Code :

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

1
2
3
4
5
6
7
8
9
Ligne 1 = Petit enfant1 / GP1 / GP2
Ligne 2 = Petit enfant1 / GP2 / GP1
Ligne 3 = Petit enfant1 / GP3 / GP4
Ligne 4 = Petit enfant1 / GP4 / GP3
Ligne 5 = Petit enfant2 / GP1 / GP2
Ligne 6 = Petit enfant2 / GP2 / GP1
Ligne 7 = Petit enfant2 / GP3 / GP4
Ligne 8 = Petit enfant2 / GP4 / GP3
etc..

Mais je reste loin du compte.

Le passage du multiligne à une ligne sans doublons me semble augmenter la difficulté par 10 en terme de requête (en utilisant les clauses de base) et c'est tout mon souci.

Je ne cherche pas à faire faire le travail à ma place dans le sens où le travail est déjà fait. Voici ma réponse:

Code SQL :

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
SELECT 
       prenom, 
       nom,
       MAX(CASE WHEN ordre = 1 THEN grandparent_nom END) as grandparent_1,
       MAX(CASE WHEN ordre = 2 THEN grandparent_nom END) as grandparent_2,
       MAX(CASE WHEN ordre = 3 THEN grandparent_nom END) as grandparent_3,
       MAX(CASE WHEN ordre = 4 THEN grandparent_nom END) as grandparent_4
FROM (
    SELECT e.id, 
           e.prenom, 
           e.nom,
           gp.prenom || " " || gp.nom as grandparent_nom,
           ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY gp.prenom, gp.nom) as ordre 
    FROM relations p_e 
    JOIN relations gp_p  ON gp_p.enfant = p_e.parent 
    JOIN personnes gp   ON gp.id = gp_p.parent 
    JOIN personnes e     ON e.id = p_e.enfant
)
GROUP BY prenom, nom, id;

Mais je cherche surtout à simplifier le code et à éviter les clauses exotiques du genre " ROW NUMBER et PARTITION". Ca me semble compliqué et tiré par les cheveux pour rien.

J'ai aussi cette solution qui est ULTRA élégante et simple mais elle triche un peu (tous les grands parents sont casés dans une seule colonne au lieu d'avoir chacun la sienne):

Code SQL :

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

1
2
3
4
5
6
7
8
9
SELECT
    p.prenom,
    p.nom,
    GROUP_CONCAT(gp.prenom || ' ' || gp.nom, ', ')
FROM personnes p
INNER JOIN relations r1 ON r1.enfant = p.id
INNER JOIN relations r2 ON r2.enfant = r1.parent
INNER JOIN personnes gp ON r2.parent = gp.id
GROUP BY p.id

Les clauses que tu utilises sont parfaites en terme de simplicité d'écriture et de lecture et l'ensemble est très élégant mais elles ne me mènent pas vraiment là où je voudrais aller à ce stade. Avec mes connaissances, j'ai l'impression que c'est pas loin en terme d'idée mais que ça reste un cul-de-sac pour arriver au résultat voulu. Je me trompe ?

PS : Je ne compte pas utiliser ces requêtes pour un travail. j'essaye juste d'apprendre à faire simple avec les join et de maitriser l'aspect enfant/parent/grand parent dans une hiérarchie.
Je galère avec les jointures car je pense procédural (étape 1, étape 2,..) et les jointures ne le sont pas vraiment dans leur écriture. J'essaye donc d'apprendre à faire simple. Mais si tu me dis qu'on ne peut pas faire plus simple alors ok. je n'ai pas encore abordé les pivots, donc je ne sais pas à quel point ça simplifie le truc pour ce que je cherche à faire. Faudrait que j'essaye si ce n'est pas possible autrement.

[vttman]

C'est bien comme exercice ça fait travailler les méninges

Effectivement le GROUP_CONCAT c'est un PIVOT déguisé

En fait je pensais à ce genre de requête au final
qui suppose qu'un enfant à 2 parents et 2 grand-parents, sinon il faut changer les inner join en left join

et le LIMIT 1 ne garde qu'une ligne ... c'est sa fonction

Code SQL :

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
SELECT e.nom, gp.nom, gp1.nom, gp2.nom  , gp3.nom
from  personnes e
inner join  relations r_p_e 
ON e.id = r_p_e.enfant
 
inner join  relations r_gp_p 
ON r_p_e.parent = r_gp_p.enfant
inner join  personnes gp
on gp.id = r_gp_p.parent
 
inner join  relations r_gp_p1 
ON r_p_e.parent = r_gp_p1.enfant
inner join  personnes gp1
on gp1.id = r_gp_p1.parent
 
inner join  relations r_gp_p2 
ON r_p_e.parent = r_gp_p2.enfant
inner join  personnes gp2
on gp2.id = r_gp_p2.parent
 
inner join  relations r_gp_p3 
ON r_p_e.parent = r_gp_p3.enfant
inner join  personnes gp3
on gp3.id = r_gp_p3.parent
 
where 
 gp.id <> gp1.id and
 gp1.id <> gp2.id and
 gp2.id <> gp3.id
 
limit 1

[lexgotham]

Genial, merci !