Associer une extraction INNER et une extraction OUTER

**ccciolll** · 12/01/2022, 14h02

Bonjour.

Voici mon code (requête) qui ne fonctionne pas (code erreur : #1066 - Not unique table/alias: 'planning' ) :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT enfant.nom, enfant.prenom, planning.date_debut, planning.date_fin
 FROM enfant
 
  LEFT OUTER JOIN planning
   ON enfant.id = planning.id_enfant
 
  INNER JOIN planning
   ON planning.id_enfant = enfant.id
    WHERE planning.date_fin < CURDATE()
     OR (IsNull(planning.date_fin) and IsNull(planning.date_debut))

L'idée est de sortir, sur la même table issue d'une requête :
- les enfant qui ne figurent pas sur le planning, d'où le LEFT OUTER,
- les enfant dont la date_fin est passée (ou dont date_debut et date_fin sont nulles, au cas où ça arriverait sur ma table planning), d'où le INNER.

Chacune des requête donne un résultat satisfaisant quand je les traite individuellement (c'est d'ailleurs pour le moment la solution échappatoire que je compte utiliser pour les afficher en PHP).

Mais je pense que je passe à côté de qqchose (et probablement un truc simple) pour me permettre de les traiter toutes les deux en un coup (et obtenir un seul tableau de résultat). Les recherches faites sur le code d'erreur ne m'ont pas mené à des discussions qui m'auraient permis de réussir.
Ou alors ce n'est pas réalisable. Ou réalisable mais de façon compliquée (et dans ce cas, je préfère garder 2 requêtes simples, pas la peine de trimballer un code auquel je ne comprendrai rien, c'est pour un exercice).

Avez vous une idée de ce que j'ai raté ?

**tatayo** · 12/01/2022, 14h07

Bonjour,
Comme l'indique le message d'erreur, tu as utilisé 2 fois le même alias dans la requête.
A priori tu n'as besoin que d'une seul jointure:

Code :

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

1
2
3
4
5
6
7
8
 
SELECT enfant.nom, enfant.prenom, planning.date_debut, planning.date_fin
 FROM enfant
 
  LEFT OUTER JOIN planning
   ON enfant.id = planning.id_enfant 
    WHERE planning.date_fin < CURDATE()
     OR (IsNull(planning.date_fin) and IsNull(planning.date_debut))

Par contre il nous manque un élément: peut-il y avoir plusieurs lignes dans la table planning pour une même ligne de la table enfant ?

Tatayo.

**ccciolll** · 12/01/2022, 14h37

Merci Tatayo !

Je pense qu'à la base, j'avais mal compris le fonctionnement de OUTER JOIN, je pensais qu'il ne servait QUE à extraire les éléments présents dans la table A mais pas la B. Mais apparemment c'est un genre de INNER JOIN (il fait le boulot du INNER) qui ajoute EN PLUS les éléments OUTER.

Bien bien bien.

Et du coup, ta question qui vient ensuite, eh ben j'avoue que je n'avais pas pensé à ce problème. Mais effectivement, si un enfant apparaît plusieurs fois dans le planning, dont une qui répond à mes valeurs et une autre qui n'y répond pas, je risque de le voir dans mes résultats, alors qu'il ne devrait pas y être. C'est plus compliqué que ce que j'avais pensé !

[edit] je confirme, les enfants qui sont au planning actuel mais qui y ont figuré aussi par le passé (garde terminée) sont sélectionné par ma requête, alors qu'ils ne devraient pas. Je n'y avais pas pensé car dans ma table planning le cas ne s'était pas présenté d'un enfant figurant pls fois au planning. Crotte et re-crotte ! Comment je vais faire ?[/edit]

**tatayo** · 12/01/2022, 14h45

En fait une jointure externe (gauche) renvoie les lignes de la table de gauche, et les lignes de la table de droite quand elles existent.
Dans l'exemple, sans la clause WHERE, la requête renvoie toutes les lignes de la table enfant, et pour chacune les potentielles lignes de la table planning correspondant au critère de jointure.
C'est d'ailleurs pour cela qu'il faut mettre le test sur la date dans la jointure, sinon celle-ci se transforme implicitement en jointure interne.

Concernant le message d'erreur original, la même table apparait 2 fois dans la requête (ligne 4 et 7), mais sans alias.
Du coup dans la clause WHERE on ne sait pas de quelle occurrence de cette table on fait référence.
Dans ce cas il faut utiliser un alias différent pour chaque occurrence de cette table.

Pour en revenir à la question initiale, il faudrait nous donner un jeu d'essai qui prend en compte tous les cas de figure, avec le résultat attendu correspondant.
Ainsi on pourra être plus précis dans nos réponses.

Tatayo.

**ccciolll** · 12/01/2022, 15h14

Je ne comprends pas, quand tu dis :

Envoyé par tatayo

Dans l'exemple, sans la clause WHERE, la requête renvoie toutes les lignes de la table enfant, et pour chacune les potentielles lignes de la table planning correspondant au critère de jointure.

Car quand j'utilise le code que tu as donné, je n'obtiens pas TOUTES les lignes de la table enfant, mais seulement celles dont enfant.id ne figure pas dans planning.id_enfant (donc les enfants qui ne figurent pas au planning). Ce qui est d'ailleurs exactement mon souhait dans l'utilisation du LEFT.

Envoyé par tatayo

C'est d'ailleurs pour cela qu'il faut mettre le test sur la date dans la jointure, sinon celle-ci se transforme implicitement en jointure interne.

Et puis surtout parce que je veux extraire les enfant du planning dont la date est passée.

Envoyé par tatayo

Concernant le message d'erreur original, la même table apparait 2 fois dans la requête (ligne 4 et 7), mais sans alias.
Du coup dans la clause WHERE on ne sait pas de quelle occurrence de cette table on fait référence.
Dans ce cas il faut utiliser un alias différent pour chaque occurrence de cette table.

Je ne connais pas encore la notion d'alias, j'en suis au tout début du SQL.

Envoyé par tatayo

Pour en revenir à la question initiale, il faudrait nous donner un jeu d'essai qui prend en compte tous les cas de figure, avec le résultat attendu correspondant.
Ainsi on pourra être plus précis dans nos réponses.

Oui, clairement, il faut que je mette ça à plat.
Bon, l'idée est que je voulais créer une page pour désinscrire un enfant.
Mais pour que ça se fasse bien, il faut que l'enfant ne figure pas au planning actuel (le planning comporte id_enfant, date_debut et date_fin)
Donc, je voulais extraire une liste (dont les critères sont précisés ci-dessous) d'enfants qu'il est possible de désinscrire sans risque, et ne proposer que ces enfants-là dans le formulaire de désinscription.

priorité 1 :
Critères d'exclusion (ne pas faire figurer sur la liste):
- Enfant dont la date_fin est plus tard que la date du jour (une garde est en cours dont la fin est définie)
- Enfant dont la date_fin est NULLE mais la date_debut n'est pas NULLE (une garde est en cours dont la fin n'est pas définie (c'est le cas normal de mon outil actuellement))

priorité 2 uniquement si priorité 1 est faux :
Critères d'éligibilité :
- Enfant ne figurant pas au planning (ont été ajoutés mais jamais planifiés).
- Tous les autres enfants du planning (ceux dont la date de fin est passée, ceux dont date début et fin sont nulles (mauvaises utilisation du planning))

Et en fait, en le posant comme ça et en essayant de l'écrire pour vous l'expliquer, je m'aperçois que j'ai pris le pb à l'envers.

Plutôt que chercher quels enfants retenir, ce serait peut-être plus facile de détecter lesquels ne PAS retenir et afficher tous les autres (ça m'éviterait en plus des doublons si un enfant est pls fois au planning).

Il faut que je trouve comment créer une instructions inversée (« cherche ceci et donne moi le reste »).

**ccciolll** · 12/01/2022, 15h32

Bon, j'ai tenté de faire l'instruction inversée

Code :

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

1
2
3
4
5
6
7
SELECT enfant.id, enfant.nom, enfant.prenom, planning.date_debut, planning.date_fin
 FROM enfant
 
  LEFT OUTER JOIN planning
   ON enfant.id = planning.id_enfant 
    WHERE (IsNull(planning.date_fin) and IsNull(planning.date_debut))
     OR NOT planning.date_fin > CURDATE()

Mais j'ai le même souci qu'avec la précédente : il me sort quand-même les enfants qui ont au planning à la fois des lignes en cours et des lignes terminées.

(au passage, j'ai oublié d'exclure ceux qui ont une date fin NULLE et une date de début NON NULLE (je ne sais pas comment inverser IsNull) mais ça ne change rien au fond du problème qui est que je n'arrive pas à exclure ceux qui doivent l'être)

**tatayo** · 12/01/2022, 15h34

Envoyé par LoicBahuEtu

Je ne comprends pas, quand tu dis :
Car quand j'utilise le code que tu as donné, je n'obtiens pas TOUTES les lignes de la table enfant, mais seulement celles dont enfant.id ne figure pas dans planning.id_enfant (donc les enfants qui ne figurent pas au planning). Ce qui est d'ailleurs exactement mon souhait dans l'utilisation du LEFT.

C'est pour cela que j'indiquais "sans la clause WHERE".

En avançant par étapes, on devrait facilement trouver la solution:
1. Récupérer tous les enfants:

Code SQL :

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

1
2
3
 
select e.*
from enfants as e

2. Joindre les éventuelles lignes de planning correspondantes, sans aucun filtre pour l'instant:

Code SQL :

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

1
2
3
4
 
select e.nom, e.prenom,p.date_debut,p.date_fin
from enfant as e
left outer join planning as p  on e.id = p.id_enfant

3. Modifier la jointure, pour ne prendre que les lignes de planning qui conduiront à "l'exclusion":

Code SQL :

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

1
2
3
4
 
select e.nom, e.prenom,p.date_debut,p.date_fin
from enfant as e
left outer join planning as p on e.id = p.id_enfant and (p.date_fin < CURDATE() or (p.date_debut < CURDATE() and p.date_fin is null))

4. Filtrer le tout pour ne garder que les lignes qui vont bien:

Code SQL :

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

1
2
3
4
5
 
select e.nom, e.prenom,p.date_debut,p.date_fin
from enfant as e
left outer join planning as p on e.id = p.id_enfant and (p.date_fin < CURDATE() or (p.date_debut is not null and p.date_fin is null))
where p.id_enfant is null

Ainsi on ne garde que les lignes de la table enfant, pour lesquelles on ne trouve pas de ligne dans la table planning avec soit une date de fin dans le future, soit aucune date de renseignée.

Tatayo.

**ccciolll** · 12/01/2022, 16h34

Envoyé par tatayo

4. Filtrer le tout pour ne garder que les lignes qui vont bien:

Code SQL :

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

1
2
3
4
5
 
select e.nom, e.prenom,p.date_debut,p.date_fin
from enfant as e
left outer join planning as p on e.id = p.id_enfant and (p.date_fin < CURDATE() or (p.date_debut is not null and p.date_fin is null))
where p.id_enfant is null

Ainsi on ne garde que les lignes de la table enfant, pour lesquelles on ne trouve pas de ligne dans la table planning avec soit une date de fin dans le future, soit aucune date de renseignée.
Tatayo.

Ach, je ne sais pas pourquoi, Tatayo, mais ta proposition de code ne fonctionne pas.
À la fin je ne vois que les enfants totalement absents de la table planning.
(pourtant, ce que tu exprimes est exactement ce que je veux faire : ne garder que les lignes enfant pour lesquelles blabla sur la table planning)

---

Voici un autre code de mon cru (j'ai ajouté les id enfant et planning pour y voir plus clair au niveau des résultat sur myadmin) :

Avec ce code , j'arrive presque au bon résultat.

Code :

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

1
2
3
4
5
select e.id, e.nom, e.prenom, p.id, p.date_debut,p.date_fin
from enfant as e
left outer join planning as p 
on e.id = p.id_enfant 
and (p.date_fin < CURDATE() or (p.date_debut is null and p.date_fin is null))

Tous les enfants à sélectionner apparaissent.
Mais il reste aussi un enfant qui a 2 lignes planning, une terminée et une en cours.

Pour simplifier les choses, j'ai vérifié la propreté de ma table, donc les contraintes seraient uniquement

NE PAS RETENIR : enfants qui ont au moins une ligne planning dont date_debut est déterminée (non nulle, peu importe sa valeur) et une date_fin nulle.
RETENIR : tous les autres, y compris ceux qui sont absent de la table planning.

L'idée pourrait être d'avoir un code qui dit « prendre tous les enfants de table enfant, exclure ceux qui ont une ligne qui répond au critère (début non nul + fin nulle) »

**ccciolll** · 12/01/2022, 16h39

Pour info, j'avais aussi créé une page qui liste tous les enfants actuellement gardés, avec le code suivant

Code :

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

1
2
3
4
SELECT enfant.nom, enfant.prenom, planning.date_debut, planning.date_fin 
FROM enfant 
INNER JOIN planning  ON planning.id_enfant=enfant.id
WHERE planning.date_debut <= CURDATE() AND (planning.date_fin > CURDATE() or IsNull(planning.date_fin))

qui pourrait d'ailleurs être simplifié en

Code :

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

1
2
3
4
SELECT enfant.nom, enfant.prenom, planning.date_debut, planning.date_fin 
FROM enfant 
INNER JOIN planning  ON planning.id_enfant=enfant.id
WHERE planning.date_debut <= CURDATE() AND IsNull(planning.date_fin)

puisque je sais que ma base ne comporte pas de date_fin supérieure au jour en cours et que je n'ai pas mis d'outil qui permet de faire ça (l'outil arrêter une garde met la date du jour)
Voire même, changer « planning.date_debut <= CURDATE() » par « planning.date_debut is not null » si c'est possible.

**ccciolll** · 12/01/2022, 16h41

Donc si j'arrive à faire une requête qui va extraire toute la table enfant au complet.
Et ensuite enlever tous les enfants qui remplissent le critère « actuellement gardé »,
Ça devrait marcher.

Plus facile à écrire qu'à coder !

**ccciolll** · 12/01/2022, 17h52

Il faudrait un truc du genre

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
 
// extraire tous les noms
SELECT * 
FROM enfant
 
// et en exclure
« code pour exclure ce qui est entre {} »
 
// sélectionner les enfants actuellement gardés
    {
    SELECT enfant.id 
    FROM enfant 
    INNER JOIN planning
    ON planning.id_enfant=enfant.id
    WHERE planning.date_debut <= CURDATE() AND IsNull(planning.date_fin)
    }

Car toutes les variantes de code que j'essaye, j'obtiens une liste d'enfants avec :
- les enfants absents du planning (jamais entrés dedans)
- les enfants présents au planning mais avec date_fin dépassée (garde terminée)

mais je n'arrive JAMAIS à exclure les enfants qui ont une garde en cours ET une garde terminée.

Ce qui me pose problème ce sont donc les enfant qui ont plusieurs ligne au planning dont les critères sont contradictoires et que je veux exclure de mon résultat.

**Waldar** · 12/01/2022, 18h21

Un petit jeu d'essai d'une dizaine de lignes pour les deux tables qui illustre tous les cas de figure serait le bienvenu.

**ccciolll** · 12/01/2022, 21h03

OK.
Est-ce que comme ça, ça va ?

Table enfant

id	nom	prenom
1	Garde	Terminée
2	Gardes	Diverses
3	Garde	EnCours
4	Jamais	Gardé

Table planning

id	id_enfant	date_debut	date_fin
11	1	2016	2017
12	2	2017	2018
13	2	2019	NULL
14	3	2020	NULL

On a les 4 cas de figure.

enfant.id 1 qui a une seule garde terminée (planning.id 11)
enfant.id 2 qui a 2 gardes, une terminée (planning.id 12), une en cours (planning.id 13)
enfant.id 3 qui a une seule garde en cours (planning.id 14)
enfant.id 4 qui n'a pas encore de garde

Critères :

A) Critère d'exclusion (prioritaire sur le B) :
A1 : date_debut précisée ET date_fin nulle

B) Critères de sélection :
B1 : date_fin dépassée
OR
B2 : enfant absent du planning

Critères

planning.id 11 > critère B1 > enfant.id 1 retenu
planning.id 12 > critère B1 > enfant.id 2 retenu
planning.id 13 > critère A1 > enfant.id 2 exclu
planning.id 14 > critère A1 > enfant.id 3 exclu
absence du tableau planning (left outer) > critère B2 > enfant.id 4 retenu

soit, par enfant.id
1 = retenu
2 = retenu, exclu
3 = exclu
4 = retenu

ou par critère
retenu = 1, 2, 4
exclu = 2, 3

Résultats
Avec le CODE que j'arrive à faire,
je vais obtenir les enfant.id
1 = retenu
2 = retenu
4 = retenu

Car le critère RETENU sur enfant.id 2 dans le cas planning.id 12 est considéré SÉPARÉMENT et n'est pas corrélé au critère EXCLU du même sur enfant.id 2 dans le cas planning.id 13

Je ne sais pas comment combiner pour que le critère EXCLU puise passer en « 2eme couche » sur la liste des enfant.id RETENU (ou tout simplement sur la liste des enfant complète, ce qui donnerait le même résultat dans mon cas).

En fait, il me faudrait une requête inversée du genre
ensemble des enfant.id de table enfant - (ensemble des enfant.id qui répondent au critère excluant)
Il n'y a pas besoin du critère RETENU (j'étais parti sur cette piste à cause du pb de left outer, au départ), mais uniquement du critère EXCLU (issu de la table planning) à appliquer à l'ensemble de la table enfant.

Je ne sais pas si c'est clair, je ne vois pas comment l'expliquer.

**Waldar** · 13/01/2022, 16h19

J'ai mis les informations dans un dbfiddle :
https://dbfiddle.uk/?rdbms=postgres_...a3521f6f2fc430

Si je comprends bien, en résultat vous voulez les enfants 1 et 4 ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select *
  from enfant as e
 where not exists (select null
                     from planning as p
                    where p.id_enfant   = e.id
                      and current_date >= date_debut
                      and current_date <  coalesce(date_fin, date '9999-12-31'));
 
id  nom     prenom
--  ------  --------
 1  Garde   Terminée
 4  Jamais  Gardé

**ccciolll** · 13/01/2022, 17h59

Oui, c'est bien ce résultat que j'attends.
Et effectivement le code indiqué a fonctionné lors de mes tests.

Je ne comprends pas tout à fait ce qu'il fait.
j'ai vu sur internet que coalesce conserve le premier résultat trouvé, mais je ne comprends pas tout à fait sa fonction ici, le test sur curdate et une date maximale me semble redondant.
Je pense en tout cas que ce n'est pas lui qui permet d'exclure les id_enfant qui figurent pls fois dans la table planning
(ce qui était mon point bloquant des requêtes que j'avais formulées).

Comme la requête comporte une négation en entrée suivie d'un null, puis plusieurs critères, je n'arrive pas à la visualiser de tête pour comprendre son action.

Bon, en tout cas, ça fonctionne.

un grand merci !

[edit] Et voilà, il y a maintenant une requête sql qui porte le nom by_waldar dans mon site de test [/edit]