problème de jointure

[sky_perrinos]

Bonjour à tous!
J'ai un modèle permettant de manipuler des écoles et des personnes y travaillant:

tbl_ecole (ecole_id, nom)
tbl_site (site_id, ecole_id, site_adresse) // parcequ'une école peut avoir différents sites
tbl_personne (perso_id, perso_nom, perso_prenom, site_id)
tbl_contact(contact_id, perso_id) // table intermédiaire
lnk_contact_fonction(conta_id, fonc_co_id) //table de lien entre contact et fonctions

Objectif: Je dois faire ressortir pour toutes les écoles, le nom des personnes occupant 2 fonctions précises (directeur et secrétaire, id de fonction 26 et 84)
Problème: j'utilise un inner join, donc lorsqu'une école n'a personne pour l'une des 2 fonctions voulues, l'école entière disparaît des résultats au lieu de mettre un simple 'null' dans la fonction concernée.

J'ai essayé de jouer avec les left / right / full inner join, mais ça n'a rien donné (le tout en rappellant deux foix par des alias les trio personne/contact/lnk_contact_fonction):

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT DISTINCT te.ecole_id, 
	ts.site_adresse,
	tp_directeur.perso_nom AS 'directeur',
	tp_secretaire.perso_nom AS 'secrétaire'
 
FROM  TBL_ECOLE te
	INNER JOIN TBL_SITE ts ON ts.ecole_id = te.ecole_id
 
	LEFT OUTER JOIN TBL_PERSONNE tp_directeur ON tp_directeur.site_id = ts.site_id
	LEFT OUTER JOIN TBL_CONTACT tc_directeur ON tc_directeur.perso_id = tp_directeur.perso_id
	LEFT OUTER JOIN LNK_CONTACT_FONCTION lcf_directeur ON lcf_directeur.conta_id = tc_directeur.conta_id
 
	LEFT OUTER JOIN TBL_PERSONNE tp_secretaire ON tp_secretaire.site_id = ts.site_id
	LEFT OUTER JOIN TBL_CONTACT tc_secretaire ON tc_secretaire.perso_id = tp_secretaire.perso_id
	LEFT OUTER JOIN LNK_CONTACT_FONCTION lcf_secretaire ON lcf_secretaire.conta_id = tc_secretaire.conta_id
 
WHERE lcf_directeur.fonct_co_id IN (26)
AND lcf_secretaire.fonct_co_id IN (84)

Quelqu'un sait comment je pourrai faire pour avoir toutes les écoles et un simple 'null' dans la colonne 'secrétaire' quand personne n'est indiqué pour cette fonction?

Merci à tous!

[punkoff]

Bonjour,

Pour afficher vos fonction par école le left outer join est la bonne solution.

Concernant la sélection de plusieur fonction, orientez vous vers un "EXISTS" (et un "OR" dans la sous-requete associée sur les 2 fonctions recherchées) afin de ne pas avoir votre problème actuel

[Waldar]

Il faut faire remonter vos filtres dans vos jointures :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT te.ecole_id, 
    ts.site_adresse,
    tp_directeur.perso_nom AS 'directeur',
    tp_secretaire.perso_nom AS 'secrétaire'
 
FROM  TBL_ECOLE te
    INNER JOIN TBL_SITE ts ON ts.ecole_id = te.ecole_id
 
    LEFT OUTER JOIN TBL_PERSONNE tp_directeur ON tp_directeur.site_id = ts.site_id
    LEFT OUTER JOIN TBL_CONTACT tc_directeur ON tc_directeur.perso_id = tp_directeur.perso_id
    LEFT OUTER JOIN LNK_CONTACT_FONCTION lcf_directeur ON lcf_directeur.conta_id = tc_directeur.conta_id AND lcf_directeur.fonct_co_id = 26
 
    LEFT OUTER JOIN TBL_PERSONNE tp_secretaire ON tp_secretaire.site_id = ts.site_id
    LEFT OUTER JOIN TBL_CONTACT tc_secretaire ON tc_secretaire.perso_id = tp_secretaire.perso_id
    LEFT OUTER JOIN LNK_CONTACT_FONCTION lcf_secretaire ON lcf_secretaire.conta_id = tc_secretaire.conta_id AND lcf_secretaire.fonct_co_id = 84

[sky_perrinos]

Bonjour Waldar,

Super ça marche nickel!

Par contre je ne comprends pas pourquoi ça marche.... quelle est la différence entre mettre la restriction sur l'id de fonction dans les jointures plutôt que dans la clause where???

Merci beaucoup en tout cas, au moins ça marche!!

[CinePhil]

Si tu mets une condition de restriction sur la table de droite dans une jointure gauche (réciproquement sur la table de gauche dans une jointure droite), c'est comme si tu faisais un INNER JOIN.

Voir ce message pour explication détaillée.

Comme ce problème revient régulièrement, j'en ai profité pour transformer le message cité en article de mon blog.

[SQLpro]

ATTENTION : pas obligatoirement dans la jointure. Cela peut aussi être dans le where mais avec un OR .... IS NULL. Tout dépende de votre besoin !

A +

[CinePhil]

Exact, j'avais oublié cette possibilité.
Que vaut-il mieux alors ?
1)

Code :

1
2
3
FROM A
LEFT OUTER JOIN B ON A.colA = B.colB
  AND B.col2 = 'une_valeur'

2)

Code :

1
2
3
4
FROM A
LEFT OUTER JOIN B ON A.colA = B.colB
WHERE B.col2 = 'une_valeur'
  OR B.ColB IS NULL

Ça fait une différence du point de vue performances ?
Est-ce que ça donne d'ailleurs le même résultat ?

[Waldar]

Les deux requêtes ne renvoient pas forcément le même résultat, en reprenant les données de votre blog, mais en changeant le booléen de la table B de 0 en null :

Code :

1
2
3
4
5
6
7
8
9
10
11
idA         valA
----------- ----
1           toto
2           titi
3           tata
 
 
idB         idA         valB booleen
----------- ----------- ---- -----------
1           2           riri NULL
2           3           fifi 1

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen 
  FROM A 
       LEFT OUTER JOIN B
         ON B.idA     = A.idA 
        AND B.booleen = 1;
 
idA         valA idB         valB booleen
----------- ---- ----------- ---- -----------
1           toto NULL        NULL NULL
2           titi NULL        NULL NULL
3           tata 2           fifi 1

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen 
  FROM A 
       LEFT OUTER JOIN B
         ON B.idA     = A.idA 
 WHERE B.booleen = 1
    OR B.booleen IS NULL;
 
idA         valA idB         valB booleen
----------- ---- ----------- ---- -----------
1           toto NULL        NULL NULL
2           titi 1           riri NULL
3           tata 2           fifi 1

Donc comme dit SQLPro, ça dépend du besoin !

[CinePhil]

C'est bien ce qu'il me semblait mais je n'ai pas pris le temps de tester.

[SQLpro]

Le filtre dans le jointure est évalué avant la mise en jointure. Il renvoie donc toutes les lignes de la tables externe, mais pour la table interne, seulement les lignes valuées avec le critère. La cardinalité est celle de la table externe augmenté du produit cartésien de la table interne s'il y a lieu.

Le filtre dans le where exclue les lignes qui ne répondent pas au critère. Certaines lignes de la table externe seront donc rejetées.

A +